0Pricing
SQL Interview Prep · Lesson

RIGHT and FULL OUTER JOIN Semantics

When each is needed and how to rewrite RIGHT as LEFT.

RIGHT and FULL OUTER JOIN Semantics is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Beyond LEFT JOIN

Once you understand LEFT JOIN, interviewers test the mirror image and the union of both: RIGHT JOIN and FULL OUTER JOIN.

  • RIGHT JOIN preserves every row of the right table.
  • FULL OUTER JOIN preserves unmatched rows from both tables.

This lesson defines each precisely and shows the rewrite trick interviewers love: any RIGHT JOIN can become a LEFT JOIN.

RIGHT JOIN Defined

A RIGHT JOIN (or RIGHT OUTER JOIN) keeps every row from the right table, the one written after the JOIN keyword. Unmatched right rows appear with NULL in the left-table columns.

It is the exact mirror of LEFT JOIN. Where LEFT preserves the first-named table, RIGHT preserves the second-named one.

SELECT c.name, o.amount
FROM orders o
RIGHT JOIN customers c
  ON o.customer_id = c.id;
-- keeps ALL customers, even those
-- with no order (Carol -> amount NULL)

The Tables Again

Same data as before. Carol (customer 3) has no order. Order rows all reference an existing customer here, so when customers is the preserved side, no order is orphaned.

-- customers           orders
-- 1 | Alice           10 | 1 | 50
-- 2 | Bob             11 | 1 | 75
-- 3 | Carol           12 | 2 | 20

Rewriting RIGHT as LEFT

The interview punchline: RIGHT JOIN is rarely used in practice because you can always flip the table order and use a LEFT JOIN. The two queries below are logically identical.

Many style guides ban RIGHT JOIN entirely because reading left-to-right and always preserving the left table is easier to reason about.

-- RIGHT JOIN
SELECT c.name, o.amount
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;

-- Equivalent LEFT JOIN (preferred)
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

FULL OUTER JOIN Defined

A FULL OUTER JOIN keeps unmatched rows from both tables. Think of it as a LEFT JOIN and a RIGHT JOIN combined:

  • Matched pairs appear normally.
  • Left rows with no match: right columns NULL.
  • Right rows with no match: left columns NULL.

It answers "show me everything from both sides, lining up the matches."

SELECT c.name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o
  ON o.customer_id = c.id;

When FULL OUTER Shines

FULL OUTER JOIN is the go-to for reconciliation: comparing two datasets that should match but might not.

Imagine a orders table and a separate payments table. A FULL OUTER JOIN on order id surfaces orders with no payment and payments with no matching order in one result, each flagged by NULLs on the missing side.

SELECT o.id AS order_id, p.id AS payment_id
FROM orders o
FULL OUTER JOIN payments p
  ON p.order_id = o.id
WHERE o.id IS NULL OR p.id IS NULL;
-- rows where one side is missing

Dialect Awareness

An interviewer may probe portability. MySQL has no FULL OUTER JOIN keyword (through version 8). Postgres, SQL Server, and Oracle support it.

In MySQL you emulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION (which removes the duplicated matched rows). Knowing this distinction signals real-world experience.

-- FULL OUTER JOIN emulated in MySQL
SELECT c.name, o.id FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.id FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

The Four Join Types at a Glance

Hold this table in your head. "Preserved" means unmatched rows survive with NULLs.

  • INNER JOIN: only matched rows. Nothing preserved.
  • LEFT JOIN: all left rows preserved.
  • RIGHT JOIN: all right rows preserved.
  • FULL OUTER JOIN: both sides preserved.

Every outer-join interview question reduces to choosing which side(s) to preserve.

Choosing Between Them

Pick the join by asking "which unmatched rows must I keep?"

  • Keep the driving/main table's rows only: LEFT JOIN (put the main table first).
  • Need both unmatched sets for reconciliation: FULL OUTER JOIN.
  • Only matches matter: INNER JOIN.

You almost never actively choose RIGHT JOIN, you reframe it as LEFT.

A Reconciliation Example

Put FULL OUTER JOIN to work labeling each row's status. CASE over the NULL pattern tells you which side is missing, the classic audit query.

SELECT
  COALESCE(o.id, p.order_id) AS ord,
  CASE
    WHEN p.id IS NULL THEN 'no payment'
    WHEN o.id IS NULL THEN 'orphan payment'
    ELSE 'matched'
  END AS status
FROM orders o
FULL OUTER JOIN payments p ON p.order_id = o.id;

Common Misconception

Candidates often think FULL OUTER JOIN returns the Cartesian product of unmatched rows. It does not. Unmatched rows on each side appear exactly once, paired with NULLs, never multiplied against each other.

Matched rows still follow normal join multiplication (one output row per matching pair), the same fan-out rule as any join.

Quick Check

An interviewer asks you to rewrite a query for a team that bans RIGHT JOIN.

Recap

RIGHT JOIN preserves the right table; FULL OUTER JOIN preserves both. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping table order, which is why teams favor LEFT.

  • FULL OUTER is ideal for reconciling two datasets.
  • MySQL lacks FULL OUTER; emulate it with LEFT + RIGHT + UNION.
  • Choose a join by deciding which unmatched rows to keep.
  • Unmatched rows appear once with NULLs, never as a Cartesian product.

Frequently asked questions

Is the “RIGHT and FULL OUTER JOIN Semantics” lesson free?

Yes — the full text of “RIGHT and FULL OUTER JOIN Semantics” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “RIGHT and FULL OUTER JOIN Semantics”?

When each is needed and how to rewrite RIGHT as LEFT. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “RIGHT and FULL OUTER JOIN Semantics” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. LEFT JOIN and Preserving Unmatched Rows
  2. RIGHT and FULL OUTER JOIN Semantics
  3. Finding Rows With No Match (Anti-Join)
  4. The WHERE-on-Outer-Join Trap
← Back to SQL Interview Prep