RIGHT JOIN and FULL OUTER JOIN
Symmetry, NULL on Both Sides, and the MySQL Workaround
LinkedIn Hook
"Your reconciliation report missed every customer who had no orders AND every order that had no customer. You wrote a LEFT JOIN when you needed a FULL OUTER JOIN."
Most developers learn LEFT JOIN, declare victory, and never touch its two siblings. RIGHT JOIN gets dismissed as "just a LEFT JOIN written backwards." FULL OUTER JOIN gets dismissed as "I have never needed it." Then a finance team asks for a reconciliation: every payment in the bank statement matched against every payment in the ledger, with both unmatched sides clearly visible. A LEFT JOIN shows you only one side. A RIGHT JOIN shows you only the other. A FULL OUTER JOIN is the only join in SQL that shows you BOTH the rows that are missing from the right AND the rows that are missing from the left -- in a single pass.
And then you try to run it on MySQL and the parser explodes, because MySQL is the one major engine that does not support FULL OUTER JOIN at all. You are now writing a UNION of two LEFT JOINs and hoping nobody notices the extra de-duplication step.
RIGHT JOIN and FULL OUTER JOIN are not academic curiosities. They are the right tool for reconciliation, audit, gap analysis, and "what is missing from each side" questions. Skipping them means you keep reaching for clumsy LEFT JOIN gymnastics that work most of the time but quietly drop rows when the data does not cooperate.
In Lesson 5.4, I break down RIGHT JOIN and FULL OUTER JOIN end to end: when each one is the right answer, why RIGHT JOIN is exactly symmetric to LEFT JOIN, how FULL OUTER produces NULL on both sides, the four-quadrant mental model, the MySQL workaround using UNION, and the EXPLAIN shape that tells you the planner is doing what you asked.
Read the full lesson -> [link]
#SQL #Database #PostgreSQL #MySQL #BackendDevelopment #InterviewPrep #DataReconciliation
What You'll Learn
- What RIGHT JOIN actually does and why it is exactly symmetric to LEFT JOIN
- Why almost nobody writes RIGHT JOIN in production code and when it is genuinely the right choice
- How FULL OUTER JOIN combines the two outer joins to keep every row from both sides
- The four-quadrant mental model: matched, left-only, right-only, and how each lands in the result
- Why FULL OUTER JOIN produces NULL on BOTH sides, not just one
- The single most common use case for FULL OUTER JOIN: reconciliation and gap analysis
- How to emulate FULL OUTER JOIN on MySQL using
UNIONof a LEFT JOIN and a RIGHT JOIN - How to read EXPLAIN to confirm the planner is performing the outer join you asked for
The Two Guest Lists Analogy -- Reconciling a Wedding RSVP
You are organizing a wedding. You have two lists in front of you. The left list is INVITED -- everyone you sent an invitation to. The right list is ARRIVED -- everyone who actually walked through the door on the day. Your job is to reconcile the two so the venue can finalize the bill, and you need to answer three questions at once.
First: which guests RSVPed and showed up? That is the inner intersection -- people on both lists. Second: which guests were invited but never showed? Those are on the left list with no match on the right -- a LEFT JOIN tells you that. Third: which guests showed up that you never invited? Those are on the right list with no match on the left -- and only a RIGHT JOIN (or a FULL OUTER JOIN) tells you that. If you only run a LEFT JOIN, you will catch the no-shows but completely miss the wedding crashers, and the venue will charge you for meals you cannot account for.
A FULL OUTER JOIN does all three at once. It returns every matched pair, every left-only row (with NULLs where the right side would have been), and every right-only row (with NULLs where the left side would have been). It is the only join in SQL that guarantees you lose no row from either side. For reconciliation -- payments vs ledger, expected vs received, plan vs actual -- it is exactly the right shape.
+---------------------------------------------------------------+
| THE WEDDING RECONCILIATION |
+---------------------------------------------------------------+
| |
| INVITED (left) ARRIVED (right) |
| +-----------+ +-----------+ |
| | Alice | | Alice | -> matched |
| | Bob | | Carol | -> matched |
| | Carol | | Eve | -> right-only |
| | Dave | +-----------+ |
| +-----------+ |
| |
| FULL OUTER JOIN result: |
| +---------------+----------------+ |
| | invited | arrived | |
| +---------------+----------------+ |
| | Alice | Alice | matched |
| | Bob | NULL | no-show (left-only) |
| | Carol | Carol | matched |
| | Dave | NULL | no-show (left-only) |
| | NULL | Eve | crasher (right-only) |
| +---------------+----------------+ |
| |
| LEFT JOIN alone would MISS Eve. |
| RIGHT JOIN alone would MISS Bob and Dave. |
| FULL OUTER JOIN catches everyone. |
| |
+---------------------------------------------------------------+RIGHT JOIN -- The Mirror Image of LEFT JOIN
RIGHT JOIN keeps every row from the right table, matches them against the left table, and fills in NULLs on the left side wherever no match exists. It is the exact mirror of LEFT JOIN. In fact, the following two queries are guaranteed to return the same rows (though possibly in a different order, since neither has an ORDER BY):
-- These are equivalent
SELECT * FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;
SELECT * FROM orders o RIGHT JOIN customers c ON o.customer_id = c.id;Because of this symmetry, almost no SQL is written using RIGHT JOIN in production. The convention is to put the table you care about preserving on the left side of every join and use LEFT JOIN throughout. This makes a long join chain readable top-to-bottom: the left table is your "spine," and every joined table extends it. RIGHT JOIN breaks that mental model -- you have to read past the join keyword to figure out which side is the spine.
+---------------------------------------------------------------+
| LEFT JOIN vs RIGHT JOIN -- Same Result |
+---------------------------------------------------------------+
| |
| A LEFT JOIN B == B RIGHT JOIN A |
| |
| LEFT JOIN keeps every row from A: |
| A: [1, 2, 3, 4] |
| B: [2, 3, 5] |
| -> [(1,NULL), (2,2), (3,3), (4,NULL)] |
| |
| B RIGHT JOIN A keeps every row from A (the right table): |
| -> [(NULL,1), (2,2), (3,3), (NULL,4)] |
| ^^^^ |
| same logical rows, just column order swapped |
| |
| RULE: any RIGHT JOIN can be rewritten as a LEFT JOIN by |
| swapping the two table references. Most teams enforce |
| "LEFT JOIN only" as a style rule for consistency. |
| |
+---------------------------------------------------------------+When RIGHT JOIN Is Genuinely Useful
There is exactly one situation where RIGHT JOIN earns its keep: when you are appending a new join to a long, machine-generated, or auto-formatted query and rewriting the existing chain to swap sides would create more risk than it removes. For example, an ORM or a report builder generates FROM orders o JOIN line_items l ON ... JOIN products p ON ... and you want to add "all categories, even those without products." Rather than refactor the whole chain to put categories on the left, you can append RIGHT JOIN categories cat ON cat.id = p.category_id. It is structurally ugly but locally minimal.
In hand-written application code, the recommendation stands: prefer LEFT JOIN, swap table order to make it work. RIGHT JOIN is a syntactic convenience that costs readability.
-- Example sample tables for the rest of this lesson
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER, -- intentionally nullable for demo
total NUMERIC
);
INSERT INTO customers (name) VALUES
('Alice'), -- id 1
('Bob'), -- id 2
('Carol'), -- id 3
('Dave'); -- id 4 (no orders)
INSERT INTO orders (customer_id, total) VALUES
(1, 120.00), -- Alice
(1, 45.00), -- Alice
(3, 300.00), -- Carol
(NULL, 99.00), -- orphan order, no customer linked
(999, 17.50); -- references a customer that does not exist
-- LEFT JOIN: every customer, with their orders if any
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;
-- name | total
-- ------+-------
-- Alice | 120.00
-- Alice | 45.00
-- Bob | NULL <- no orders
-- Carol | 300.00
-- Dave | NULL <- no orders
-- RIGHT JOIN: every order, with its customer if any
-- (logically equivalent to swapping the tables in a LEFT JOIN)
SELECT c.name, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id
ORDER BY o.id;
-- name | total
-- ------+-------
-- Alice | 120.00
-- Alice | 45.00
-- Carol | 300.00
-- NULL | 99.00 <- orphan order, customer_id is NULL
-- NULL | 17.50 <- references nonexistent customer 999Notice that the LEFT JOIN finds Bob and Dave (customers with no orders) but completely misses the two orphan orders. The RIGHT JOIN catches the orphan orders but completely misses Bob and Dave. Neither query alone reconciles the whole picture -- which is exactly why FULL OUTER JOIN exists.
FULL OUTER JOIN -- Keeping Every Row from Both Sides
FULL OUTER JOIN (the keyword is FULL OUTER JOIN, or just FULL JOIN -- they mean the same thing) returns the union of a LEFT JOIN and a RIGHT JOIN. Every matched pair appears once. Every left-only row appears with NULLs on the right. Every right-only row appears with NULLs on the left. No row from either input is dropped.
This is the only join in SQL that produces NULL on both sides of the result. In a LEFT JOIN, NULLs only appear in right-side columns. In a RIGHT JOIN, NULLs only appear in left-side columns. In a FULL OUTER JOIN, you can see NULLs on either side -- and which side is NULL tells you which input the row came from.
-- Postgres: every customer AND every order, matched where possible
SELECT c.name AS customer, o.id AS order_id, o.total
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id
ORDER BY c.id NULLS LAST, o.id NULLS LAST;
-- customer | order_id | total
-- ---------+----------+--------
-- Alice | 1 | 120.00 matched
-- Alice | 2 | 45.00 matched
-- Bob | NULL | NULL left-only (customer with no orders)
-- Carol | 3 | 300.00 matched
-- Dave | NULL | NULL left-only (customer with no orders)
-- NULL | 4 | 99.00 right-only (orphan order, no customer)
-- NULL | 5 | 17.50 right-only (orphan order, customer 999)Read that result carefully. Bob and Dave appear with NULL on the right -- they exist as customers but have no matching orders. Orders 4 and 5 appear with NULL on the left -- they exist as orders but have no matching customer. Every row from both inputs is accounted for, and the NULL pattern tells you the origin of each row at a glance.
The Four-Quadrant Mental Model
The cleanest way to think about FULL OUTER JOIN is as a 2x2 grid: every row falls into exactly one of four quadrants based on whether it has a left-side match, a right-side match, both, or neither. (The "neither" quadrant is empty by definition because the row has to come from one of the two inputs.)
+---------------------------------------------------------------+
| THE FOUR QUADRANTS OF FULL OUTER JOIN |
+---------------------------------------------------------------+
| |
| | RIGHT match | NO right match |
| -----------------+----------------+-------------------- |
| LEFT match | both filled | left filled, |
| | (matched pair) | right NULL |
| -----------------+----------------+-------------------- |
| NO left match | left NULL, | (impossible -- the row |
| | right filled | must exist somewhere) |
| |
| INNER JOIN -> only the top-left quadrant |
| LEFT JOIN -> top row (matched + left-only) |
| RIGHT JOIN -> left col (matched + right-only) |
| FULL OUTER -> all three populated quadrants |
| |
+---------------------------------------------------------------+This grid is the entire mental model. Every join type in SQL is just "which of these quadrants do I want to keep?" INNER JOIN keeps the matches. LEFT and RIGHT each keep the matches plus one set of unmatched rows. FULL OUTER keeps all of them. Once you see the joins this way, the choice between them stops being memorization and starts being a question of "which quadrants do I need for this report?"
Napkin AI Visual Prompt: "Dark navy gradient (#0a0f1f -> #111a2e). Center: a 2x2 grid labeled 'FULL OUTER JOIN -- Four Quadrants'. Top-left quadrant in sky blue (#4fc3f7) labeled 'Matched (both filled)'. Top-right quadrant in light sky blue labeled 'Left only (right NULL)'. Bottom-left quadrant in rose (#ff5c8a) labeled 'Right only (left NULL)'. Bottom-right quadrant grayed out and crossed-out, labeled 'Impossible'. Below the grid, four small badges showing which join keeps which quadrants: INNER (just top-left), LEFT (top row), RIGHT (left column), FULL OUTER (all three colored). White monospace labels throughout."
The Reconciliation Use Case -- Where FULL OUTER Earns Its Keep
The single most common real-world use of FULL OUTER JOIN is reconciliation: comparing two sources of truth that should agree but might not, and producing a report that shows every discrepancy. Think bank statements vs ledger entries, expected shipments vs received shipments, last month's headcount vs this month's headcount, or upstream API records vs downstream warehouse records.
-- Reconciliation example: payments expected vs payments received
CREATE TABLE expected_payments (
invoice_id INTEGER PRIMARY KEY,
amount NUMERIC
);
CREATE TABLE received_payments (
invoice_id INTEGER PRIMARY KEY,
amount NUMERIC
);
INSERT INTO expected_payments VALUES
(101, 500.00), -- expected, will be matched
(102, 250.00), -- expected, will be matched
(103, 750.00), -- expected but never received (gap)
(104, 100.00); -- expected, but received a different amount
INSERT INTO received_payments VALUES
(101, 500.00), -- matches expected exactly
(102, 250.00), -- matches expected exactly
(104, 90.00), -- amount mismatch with expected
(999, 42.00); -- received but never expected (surprise payment)
-- Full reconciliation in a single query
SELECT
COALESCE(e.invoice_id, r.invoice_id) AS invoice_id,
e.amount AS expected,
r.amount AS received,
CASE
WHEN e.invoice_id IS NULL THEN 'unexpected'
WHEN r.invoice_id IS NULL THEN 'missing'
WHEN e.amount <> r.amount THEN 'mismatch'
ELSE 'ok'
END AS status
FROM expected_payments e
FULL OUTER JOIN received_payments r ON r.invoice_id = e.invoice_id
ORDER BY status, COALESCE(e.invoice_id, r.invoice_id);
-- invoice_id | expected | received | status
-- -----------+----------+----------+------------
-- 103 | 750.00 | NULL | missing
-- 104 | 100.00 | 90.00 | mismatch
-- 101 | 500.00 | 500.00 | ok
-- 102 | 250.00 | 250.00 | ok
-- 999 | NULL | 42.00 | unexpectedThis single query does what a LEFT JOIN, a RIGHT JOIN, and an inner join would each do separately -- and, more importantly, it does it in one pass over the data. The COALESCE(e.invoice_id, r.invoice_id) idiom is essential here: the join key is NULL on whichever side did not match, so you have to coalesce the two columns to recover the actual identifier for the row. This is the FULL OUTER JOIN equivalent of "USING(invoice_id)" -- in the USING form, the join column is automatically merged into a single column and the COALESCE happens implicitly.
-- Same query using USING -- the join column is automatically merged
SELECT
invoice_id, -- single merged column, never NULL
e.amount AS expected,
r.amount AS received
FROM expected_payments e
FULL OUTER JOIN received_payments r USING (invoice_id)
ORDER BY invoice_id;
-- USING is the cleanest way to write FULL OUTER JOIN when the join
-- columns share a name on both sides.MySQL Has No FULL OUTER JOIN -- The UNION Workaround
This is the single most painful portability issue with outer joins: MySQL does not support FULL OUTER JOIN at all. The keyword is not implemented; running FULL OUTER JOIN on MySQL produces a parser error. PostgreSQL, Oracle, SQL Server, and SQLite (since 3.39) all support it. MySQL is the holdout.
The standard workaround is to take the union of a LEFT JOIN and a RIGHT JOIN -- this gives you every matched row plus every left-only row plus every right-only row, which is exactly the FULL OUTER JOIN result. The catch is that matched rows would appear in both halves of the union, so you have to either use UNION (which de-duplicates, but only on the entire row) or carefully filter the right half to exclude already-matched rows.
-- MySQL workaround: UNION of LEFT JOIN and RIGHT JOIN
-- Pattern 1: LEFT JOIN UNION (LEFT JOIN with sides swapped, filtered)
-- This is the safest because it explicitly keeps each row exactly once.
SELECT c.name AS customer, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
UNION ALL
-- Second half: only the right-only rows (orders with no matching customer)
SELECT c.name AS customer, o.id AS order_id, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id
WHERE c.id IS NULL;
-- The WHERE filter on the second half keeps ONLY the rows that the first
-- half missed: orders that had no matching customer. Matched rows and
-- left-only rows already came from the first half.-- Pattern 2: UNION (without ALL) -- relies on de-duplication
-- Slower, hides bugs if rows look identical but should be distinct.
SELECT c.name AS customer, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
UNION -- removes exact-duplicate rows
SELECT c.name AS customer, o.id AS order_id, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;
-- DO NOT USE THIS PATTERN if any genuine duplicates could exist.
-- UNION will collapse them into a single row and silently lose data.Always prefer Pattern 1 (UNION ALL with an IS NULL filter on the right half). It is faster (no de-duplication step), it is explicit about which rows come from which side, and it does not silently lose duplicates that are legitimately distinct rows. The IS NULL filter is the FULL-OUTER-JOIN-in-MySQL idiom; once you recognize it, you will spot it in every legacy MySQL codebase that ever needed reconciliation.
+---------------------------------------------------------------+
| MYSQL FULL OUTER JOIN EMULATION |
+---------------------------------------------------------------+
| |
| SELECT ... FROM A LEFT JOIN B ON ... |
| UNION ALL |
| SELECT ... FROM A RIGHT JOIN B ON ... WHERE A.key IS NULL |
| |
| First half -> matched rows + left-only rows |
| Second half -> right-only rows ONLY (filtered) |
| |
| Why UNION ALL not UNION? |
| - UNION ALL is faster (no sort/dedup step) |
| - UNION can silently drop legitimate duplicates |
| - The IS NULL filter already prevents overlap |
| |
| Why the IS NULL filter? |
| - Without it, matched rows appear twice |
| - With it, the second half contributes ONLY right-only |
| |
+---------------------------------------------------------------+EXPLAIN -- Confirming the Planner Did What You Asked
Outer joins are not free. The optimizer cannot reorder them as freely as inner joins, the join algorithm has to handle unmatched rows on at least one side, and FULL OUTER specifically is restricted to a small number of physical join methods. Postgres, for example, can only execute FULL OUTER JOIN using a Hash Join or a Merge Join -- it cannot use a Nested Loop, because a nested-loop strategy fundamentally cannot produce the right-only rows in a single pass.
-- PostgreSQL: confirm the planner picked an outer-join-capable method
EXPLAIN ANALYZE
SELECT c.name, o.id, o.total
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;
-- Hash Full Join (cost=1.09..2.19 rows=7 width=44)
-- Hash Cond: (o.customer_id = c.id)
-- -> Seq Scan on orders o (rows=5)
-- -> Hash
-- -> Seq Scan on customers c (rows=4)
-- "Hash Full Join" is the key signal -- the planner is doing a hash
-- join with FULL outer semantics. Other valid shapes are
-- "Merge Full Join". You will NEVER see "Nested Loop Full Join"
-- because Postgres does not implement it.
-- Compare with a LEFT JOIN -- now nested loop is allowed
EXPLAIN ANALYZE
SELECT c.name, o.id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- Hash Left Join (cost=1.09..2.18 rows=5 width=44)
-- (or "Nested Loop Left Join" on small tables / indexed inner)The practical takeaway: FULL OUTER JOIN is fast enough on small to medium reconciliation queries (the ones where you actually need it), but it does not scale linearly forever. On very large reconciliations, the right move is usually to filter both sides down to a comparable window first (a date range, for example), then FULL OUTER the smaller subsets, rather than FULL-OUTERing the full tables and filtering after.
Common Mistakes
1. Reaching for FULL OUTER JOIN when LEFT JOIN is enough. FULL OUTER JOIN is the right answer when you genuinely need both sides preserved -- reconciliation, gap analysis, audit reports. Using it for "list all customers with their orders" is overkill and slower than LEFT JOIN, because the planner is forced into a more expensive physical join method. The litmus test: do you actually care about rows that exist on the right but not the left? If no, use LEFT JOIN.
2. Forgetting that the join column is NULL on whichever side did not match.
After customers c FULL OUTER JOIN orders o ON o.customer_id = c.id, both c.id and o.customer_id will be NULL for the right-only and left-only rows respectively. If you write SELECT c.id FROM ... FULL OUTER JOIN ... you will get NULLs for every order with no matching customer, even though the order itself exists. Always COALESCE(c.id, o.customer_id) or use USING(id) when you need the actual identifier.
3. Running FULL OUTER JOIN on MySQL.
The query parses fine in your IDE because the IDE highlights it as valid SQL. Then you run it on MySQL and the parser explodes. The fix is the UNION ALL ... WHERE ... IS NULL pattern. Code that needs to be portable across MySQL and Postgres should either avoid FULL OUTER JOIN entirely (and use the union pattern everywhere) or be wrapped in a database abstraction that emits the right SQL per engine.
4. Using UNION instead of UNION ALL in the MySQL workaround.
UNION removes exact-duplicate rows, which sounds helpful but silently destroys legitimately distinct rows that happen to share the same column values (two orders for the same amount on the same day, for example). Always use UNION ALL and put the IS NULL filter on the right-side half so the matched rows are not double-counted in the first place.
5. Writing RIGHT JOIN in production code. RIGHT JOIN is mathematically equivalent to a LEFT JOIN with the table order swapped, but it forces every reader of the query to mentally reverse the side they are preserving. Style guides at most companies forbid it for exactly this reason. Use LEFT JOIN consistently and put the table you want to preserve on the left side of the chain.
Interview Questions
1. "What is the difference between LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN, and when would you use each?"
LEFT JOIN keeps every row from the left table and matches the right table where possible, filling in NULLs on the right side for unmatched rows. RIGHT JOIN is the exact mirror -- every row from the right table is preserved, with NULLs on the left for unmatched. FULL OUTER JOIN keeps every row from both tables: matched pairs appear once, left-only rows appear with NULLs on the right, and right-only rows appear with NULLs on the left. You use LEFT JOIN almost all the time because the convention is to put the table you care about preserving on the left side. You use FULL OUTER JOIN when you need both unmatched sets at once -- reconciliation reports, gap analysis, "what is in A but not B and what is in B but not A in a single query." RIGHT JOIN is rarely written because it can always be rewritten as a LEFT JOIN with the table order swapped, and most style guides prefer the consistency of always using LEFT JOIN.
2. "Why would you use FULL OUTER JOIN over running a LEFT JOIN and a RIGHT JOIN separately?"
Three reasons. First, it is one query instead of two, which means one round trip to the database and one execution plan to optimize -- always cheaper than two queries that touch the same tables. Second, it is one logical operation, which is much easier to reason about and to embed in larger queries (CTEs, subqueries, views). Third, and most importantly, FULL OUTER JOIN guarantees that every row appears exactly once, in the same row format, with NULLs marking which side it came from. If you union the results of a LEFT JOIN and a RIGHT JOIN by hand, matched rows naturally appear in both halves and you have to either de-duplicate (which is fragile) or filter the second half with IS NULL on the join key (which works but is essentially recreating FULL OUTER JOIN by hand). When the database supports FULL OUTER JOIN natively, use it -- the two-query workaround is only needed when the engine, like MySQL, does not implement it.
3. "MySQL does not support FULL OUTER JOIN. How do you emulate it?"
You take the union of a LEFT JOIN and a RIGHT JOIN, with a filter on the right-side half so matched rows are not counted twice. The standard idiom is: SELECT ... FROM A LEFT JOIN B ON ... UNION ALL SELECT ... FROM A RIGHT JOIN B ON ... WHERE A.join_key IS NULL. The first half produces all matched rows plus all left-only rows. The second half produces only the right-only rows, because the WHERE A.join_key IS NULL filter excludes everything that already matched. Use UNION ALL not UNION -- UNION performs a sort and de-duplication step that is both slower and dangerous because it silently collapses legitimately-distinct rows that happen to have identical column values. The IS NULL filter is the FULL OUTER JOIN signature in MySQL code, and recognizing it is the fastest way to read legacy MySQL reconciliation queries.
4. "In a FULL OUTER JOIN, both the left-side join column and the right-side join column can be NULL. How do you reliably extract the join key for each row?"
You use COALESCE to fall back to whichever side is not NULL. For customers c FULL OUTER JOIN orders o ON o.customer_id = c.id, the safe expression is COALESCE(c.id, o.customer_id) AS id. For the matched rows, both sides are equal so it does not matter which one you pick. For left-only rows, o.customer_id is NULL and COALESCE returns c.id. For right-only rows, c.id is NULL and COALESCE returns o.customer_id. The cleaner alternative is the USING clause: customers c FULL OUTER JOIN orders o USING (id) automatically merges the two join columns into a single non-NULL column, which behaves correctly for all three quadrants. USING only works when the join column has the same name on both sides, so the COALESCE pattern is the universal fallback.
5. "Why is RIGHT JOIN almost never used in real codebases, even though it is part of the SQL standard?"
Because every RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the order of the two tables, and consistently using LEFT JOIN makes long join chains far easier to read. When you read a query, the table immediately after FROM is your "spine" -- the row source you are preserving -- and every subsequent LEFT JOIN extends that spine with optional related data. With RIGHT JOIN, the spine is on the right side of the join keyword, which forces your eye and your brain to constantly switch sides as you read down a query with multiple joins. It also makes refactoring harder: adding another join to a chain that mixes LEFT and RIGHT requires you to figure out which side is currently the spine before you can add anything. The convention "always LEFT JOIN, always preserve from the left" is enforced by most style guides and linters, and the only places you tend to see RIGHT JOIN in production are auto-generated SQL from ORMs and report builders that did not bother to canonicalize it.
Quick Reference -- RIGHT and FULL OUTER JOIN Cheat Sheet
+---------------------------------------------------------------+
| OUTER JOIN CHEAT SHEET |
+---------------------------------------------------------------+
| |
| LEFT JOIN A B -> keep all A, match B, NULL on right |
| RIGHT JOIN A B -> keep all B, match A, NULL on left |
| FULL OUTER A B -> keep all A AND all B, NULLs on both sides |
| |
| EQUIVALENCE: |
| A LEFT JOIN B == B RIGHT JOIN A |
| Always rewrite RIGHT JOIN as LEFT JOIN in app code. |
| |
| FULL OUTER JOIN SHAPE: |
| +-------------+--------------+ |
| | quadrant | result row | |
| +-------------+--------------+ |
| | matched | both filled | |
| | left-only | right NULL | |
| | right-only | left NULL | |
| +-------------+--------------+ |
| |
| EXTRACTING THE JOIN KEY: |
| COALESCE(a.id, b.id) AS id |
| -- or -- |
| USING (id) -- merges columns automatically |
| |
| MYSQL EMULATION (no FULL OUTER JOIN keyword): |
| SELECT ... FROM A LEFT JOIN B ON ... |
| UNION ALL |
| SELECT ... FROM A RIGHT JOIN B ON ... |
| WHERE A.join_key IS NULL |
| |
| PLANNER NOTES (Postgres): |
| FULL OUTER JOIN allowed methods: Hash, Merge |
| NOT allowed: Nested Loop |
| Look for "Hash Full Join" / "Merge Full Join" in EXPLAIN |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| KEY RULES |
+---------------------------------------------------------------+
| |
| 1. RIGHT JOIN is just LEFT JOIN with sides swapped |
| 2. Use LEFT JOIN consistently in app code, never RIGHT |
| 3. FULL OUTER JOIN is the only join with NULLs on both sides |
| 4. FULL OUTER JOIN is the right tool for reconciliation |
| 5. COALESCE the join keys to recover the row identifier |
| 6. USING (col) is cleaner than ON when names match |
| 7. MySQL has no FULL OUTER JOIN -- emulate with UNION ALL |
| 8. Always UNION ALL with IS NULL filter, never plain UNION |
| 9. Postgres FULL OUTER JOIN uses Hash or Merge, never NL |
| 10. Filter both sides down before FULL OUTER on huge tables |
| |
+---------------------------------------------------------------+| Concern | Wrong Way | Right Way |
|---|---|---|
| Preserving one side | Mix LEFT and RIGHT in chain | LEFT JOIN only, swap table order |
| Reconciliation | Two queries with LEFT and RIGHT | One FULL OUTER JOIN |
| MySQL FULL OUTER | FULL OUTER JOIN keyword | UNION ALL + WHERE IS NULL |
| MySQL union dedup | Plain UNION | UNION ALL with explicit filter |
| Recovering join key | SELECT a.id | COALESCE(a.id, b.id) or USING |
| Reading FULL OUTER result | Ignore NULL pattern | NULL side tells you the origin |
| EXPLAIN check | Trust the keyword | Look for Hash/Merge Full Join node |
| Huge reconciliation | FULL OUTER full tables | Filter to date window first |
Prev: Lesson 5.3 -- LEFT JOIN Next: Lesson 5.5 -- CROSS JOIN and SELF JOIN
This is Lesson 5.4 of the Database Interview Prep Course -- 12 chapters, 58 lessons.