FULL OUTER JOIN

A FULL OUTER JOIN keeps every row from both tables by running the match in both directions: it emits each matched pair once, then pads every unmatched left row and every unmatched right row with NULLs on the missing side — so the result is the set union of an INNER JOIN plus the left-only rows plus the right-only rows.

Postgres, SQL Server, and Oracle have the keyword directly:

SELECT s.student_id, s.student_name, g.grade
FROM students s
FULL OUTER JOIN grades g ON s.student_id = g.student_id;

MySQL and MariaDB do not support FULL OUTER JOIN. The standard workaround is to run a LEFT JOIN, run a RIGHT JOIN, and stitch them with UNION. The LEFT JOIN gives you every left row (matched + left-only); the RIGHT JOIN gives you every right row (matched + right-only). Glue them and you have all three regions — but the matched rows now appear in both halves, so they would be counted twice. UNION (not UNION ALL) is what removes that duplication.

That single word is the entire mechanism. UNION de-duplicates its combined output; UNION ALL does not. So the matched rows — which are identical in both halves — collapse to one copy under UNION, while the left-only and right-only rows (unique to their half) survive untouched. Get this wrong and you either double the inner rows (UNION ALL) or write a different SELECT list in each half so the matched rows look different and slip past the de-dup anyway.

Worked example

Two tables. Student 3 has no grade row; grade row for student 4 has no matching student (an orphaned record).

Students 1 and 2 match; student 3 is left-only; grade row 4 is right-only.

Step 1 — the LEFT JOIN half. Every student; NULL grade where none exists.

Step 2 — the RIGHT JOIN half. Every grade row; NULL student fields where none matches. To keep the matched rows byte-identical to Step 1, the SELECT list must coalesce the id so it never goes NULL on the join key, and select the same three columns in the same order.

Step 3 — UNION de-duplicates. Rows (1,Asha,A) and (2,Ben,B) are present in both halves — identical tuples — so UNION collapses each pair to one. (3,Cyrus,NULL) exists only in the left half and (4,NULL,C) only in the right half, so both pass through. Final result, all three regions, each row once:

The correct MySQL query

Both halves select the same three columns in the same order, and the key is coalesced so a matched row is identical no matter which half produced it. (Without the COALESCE the join key is fine here, but coalescing makes the two halves provably tuple-identical for the matched region, which is what the de-dup relies on.)

SELECT s.student_id, s.student_name, g.grade
FROM students s
LEFT JOIN grades g ON s.student_id = g.student_id

UNION

SELECT g.student_id, s.student_name, g.grade
FROM students s
RIGHT JOIN grades g ON s.student_id = g.student_id;

Here the RIGHT-half SELECT uses g.student_id on purpose: in a RIGHT JOIN it is s.* that goes NULL for orphan grades, so reading the id from g keeps row 4's id as 4 rather than NULL. s.student_name is still NULL for that row — correct, because no student matches.

Why the naive version is wrong

A common buggy attempt swaps the inner SELECT list inconsistently — e.g. selecting s.student_id in the LEFT half but g.student_id in the RIGHT half. For the matched rows both columns hold the same value, so it happens to work. But the moment one half emits a column the other doesn't (different alias order, an extra computed column, trailing whitespace from a CONCAT), the two copies of a matched row are no longer byte-identical — UNION sees them as distinct and keeps both. You silently get duplicated inner rows. The fix is mechanical: both SELECT lists must be column-for-column identical.

Pitfalls

• UNION ALL instead of UNION. Tempting because UNION has a sort/hash de-dup cost. But UNION ALL emits the matched rows twice. If you know the join is 1:1 you can instead keep UNION ALL and add WHERE s.student_id IS NULL to the RIGHT half — that filters the RIGHT half down to right-only rows, so nothing overlaps and no de-dup is needed. This is the faster idiom on large tables.
• NULLs are equal under UNION but not under =. SQL's set operators treat two NULLs as the same value for de-dup, even though NULL = NULL is unknown in a WHERE clause. So two identical NULL-bearing rows do collapse — which is what you want here, but it surprises people who expect NULL to never match.
• Mismatched column lists. The two SELECTs must have the same number of columns with compatible types, or the UNION errors out (or worse, lines up columns positionally in a way you didn't intend).
• Reading the id from the wrong side. In the RIGHT-half SELECT, pull the join key from the right table; in the LEFT half, from the left table. Pull it from the always-nullable side and orphan rows lose their key to NULL.
• Performance. The emulation scans both tables twice (once per join) plus a de-dup pass. A native FULL OUTER JOIN does it in one pass — another reason to reach for a real engine when you can.

Takeaways

• FULL OUTER JOIN = INNER rows + left-only (NULL-padded) + right-only (NULL-padded), each emitted once.
• MySQL/MariaDB lack the keyword; emulate with LEFT JOIN ... UNION ... RIGHT JOIN. UNION (never UNION ALL) is load-bearing — it removes the matched rows that both halves duplicate.
• De-dup only works if the matched rows are identical across halves, so both SELECT lists must be column-for-column the same and pull the join key from the non-nullable side.
• On big tables, prefer UNION ALL + WHERE <key> IS NULL on the RIGHT half to skip the de-dup, or just use a database with native FULL OUTER JOIN.

Sources: ISO/IEC 9075 (SQL standard, outer join semantics); PostgreSQL documentation, “Joined Tables”; MySQL Reference Manual, JOIN syntax (notes the absence of FULL OUTER JOIN); Itzik Ben-Gan, T-SQL Fundamentals (set-based join reasoning). Re-authored and deepened for this guide — fixed the sloppy “My SQL” phrasing, corrected the inconsistent SELECT lists in the emulation, and added the de-dup mechanism (why UNION, not UNION ALL) that the original omitted.

🎯 STRICT STANDOUT: Why / mental model / when-not / worked / failure / hostile panel — FULL OUTER JOIN

Why this concept exists (judgment layer)

FULL OUTER is the reconciliation join (matched + left-only + right-only). MySQL lacks the keyword; UNION vs UNION ALL is the entire correctness story of the emulation.

Mental model (install this intuition)

FULL = INNER ∪ left-only ∪ right-only. Emulation: LEFT UNION RIGHT with identical SELECT lists so matched tuples de-dup. UNION ALL doubles matches unless right half filters WHERE left_key IS NULL.

Worked example with numbers or traced steps

students 1 Asha, 2 Ben, 3 Cyrus | grades 1 A, 2 B, 4 C
LEFT half: (1,Asha,A)(2,Ben,B)(3,Cyrus,NULL)
RIGHT half: (1,Asha,A)(2,Ben,B)(4,NULL,C)
UNION de-dup → 4 rows; UNION ALL → 6 rows (matches twice)
Fast idiom: LEFT UNION ALL (RIGHT WHERE s.id IS NULL)

When NOT to use / named alternative

If you only need missing parents or missing children, use LEFT or RIGHT alone. If engine has native FULL OUTER (Postgres), use it — one pass. Do not FULL OUTER when INNER plus two anti-joins are clearer for separate pipelines.

Failure mode & ops fingerprint

Fingerprint: MySQL report row count ≈ 2× matches after UNION ALL; mismatched SELECT lists prevent de-dup; orphan grade loses id because SELECT used left key on RIGHT join half.

Hostile-panel drills (defend the decision)

Q1. Why UNION not UNION ALL in the basic MySQL emulation?
Model answer: Matched rows appear in both halves identically; UNION collapses them; UNION ALL doubles them.

Q2. Faster large-table emulation?
Model answer: LEFT JOIN UNION ALL (RIGHT JOIN … WHERE left.key IS NULL) — no de-dup sort/hash.

Q3. Do set ops treat NULL = NULL?
Model answer: Yes for de-dup membership under UNION/INTERSECT/EXCEPT; unlike WHERE equality.