SQL · Chapter 19 of 42

FULL OUTER JOIN

FULL OUTER JOIN keeps ALL rows from BOTH tables — matched pairs where possible, NULLs where not.

Rare in practice; useful for reconciliation reports.

Compat

MySQL doesn't support FULL OUTER JOIN — emulate with UNION of LEFT JOIN and RIGHT JOIN.

Example 1 (sql)

SELECT COALESCE(a.id, b.id) AS id, a.value, b.value
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id;

Output

all ids from either table

See rows present in either or both.

Example 2 (sql)

-- MySQL emulation
SELECT * FROM a LEFT JOIN b ON a.id = b.id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id = b.id;

Output

same result on MySQL

UNION trick.

💡 Note: For reconciliation, add `WHERE a.id IS NULL OR b.id IS NULL` to see only the mismatches.