FULL OUTER JOIN & UNION Emulation
IntermediateFULL OUTER JOIN returns all rows from both tables with NULLs for unmatched sides; it can be emulated in MySQL using UNION of LEFT and RIGHT JOINs.
Overview
FULL OUTER JOIN combines the results of LEFT JOIN and RIGHT JOIN: it returns all rows from both tables. Where a row on either side has no match, the columns of the unmatched table are NULL. This is useful for reconciliation queries — finding rows that exist in one dataset but not another. MySQL does not support FULL OUTER JOIN natively; it must be emulated with UNION of a LEFT JOIN and a RIGHT JOIN (or LEFT JOIN + IS NULL union). UNION removes duplicate rows (UNION ALL keeps them). For reconciliation across systems (comparing two tables), the FULL OUTER JOIN + COALESCE pattern is the standard approach.
FULL OUTER JOIN
FULL OUTER JOIN is the union of LEFT JOIN and RIGHT JOIN. Rows that match appear once with all columns populated; unmatched rows from either side appear with NULLs on the opposite side.
-- PostgreSQL: reconcile two product tables (compare legacy vs new system)
SELECT
COALESCE(legacy.sku, new_sys.sku) AS sku,
legacy.price AS legacy_price,
new_sys.price AS new_price,
CASE
WHEN legacy.sku IS NULL THEN 'Only in new system'
WHEN new_sys.sku IS NULL THEN 'Only in legacy'
ELSE 'In both'
END AS status
FROM legacy_products legacy
FULL OUTER JOIN new_products new_sys ON new_sys.sku = legacy.sku
WHERE legacy.price IS DISTINCT FROM new_sys.price -- PostgreSQL: NULL-safe !=
OR legacy.sku IS NULL
OR new_sys.sku IS NULL;MySQL Emulation with UNION
MySQL requires combining a LEFT JOIN and a RIGHT JOIN via UNION to emulate FULL OUTER JOIN. Use UNION ALL + manual deduplication for performance when the tables are large.
-- MySQL: emulate FULL OUTER JOIN with UNION
SELECT
COALESCE(l.sku, r.sku) AS sku,
l.price AS legacy_price,
r.price AS new_price
FROM legacy_products l
LEFT JOIN new_products r ON r.sku = l.sku
UNION
SELECT
COALESCE(l.sku, r.sku),
l.price,
r.price
FROM legacy_products l
RIGHT JOIN new_products r ON r.sku = l.sku
WHERE l.sku IS NULL; -- RIGHT JOIN only: rows that exist only in new_products
-- UNION vs UNION ALL
-- UNION: removes duplicate rows (adds a sort/hash dedup step)
-- UNION ALL: keeps duplicates (faster; use when you know there are none)
-- Find rows in table A not in table B (using UNION + IS NULL)
SELECT a.id, 'missing from B' AS note FROM table_a a
LEFT JOIN table_b b ON b.id = a.id WHERE b.id IS NULL
UNION ALL
SELECT b.id, 'missing from A' FROM table_b b
LEFT JOIN table_a a ON a.id = b.id WHERE a.id IS NULL;Key Points to Remember
- 1FULL OUTER JOIN = all rows from both tables; NULLs fill unmatched columns on either side.
- 2MySQL does not support FULL OUTER JOIN — emulate with LEFT JOIN UNION RIGHT JOIN WHERE IS NULL.
- 3UNION deduplicates rows (costly); UNION ALL retains duplicates (faster — prefer when safe).
- 4Use COALESCE on the key column to get a non-NULL identifier from either side.
- 5IS DISTINCT FROM (PostgreSQL) is a NULL-safe != operator useful in reconciliation queries.
- 6FULL OUTER JOIN is the standard pattern for data reconciliation and audit comparisons.
Interview Questions
Sign in to ask AriaWhat does FULL OUTER JOIN return that LEFT JOIN and INNER JOIN do not?
How do you emulate FULL OUTER JOIN in MySQL?
What is the difference between UNION and UNION ALL?
Write a query to find all records that exist in either of two tables but not in both.
Ask Aria about FULL OUTER JOIN & UNION Emulation
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.