Topic 30 of 52
FULL OUTER JOIN
Overview
FULL OUTER JOIN (or FULL JOIN) returns ALL rows from BOTH tables, with NULLs where no match exists. It is used to find data anomalies, merge datasets from different sources, and perform data reconciliation.
Syntax
sql
-- FULL OUTER JOIN: all rows from both tables
SELECT
a.id AS id_a,
b.id AS id_b,
a.name,
b.value
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.a_id;
-- Find rows only in left, only in right, or in both
SELECT
COALESCE(a.id, b.a_id) AS id,
CASE
WHEN a.id IS NULL THEN 'Only in B'
WHEN b.a_id IS NULL THEN 'Only in A'
ELSE 'In Both'
END AS source
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.a_id;
-- MySQL doesn't support FULL OUTER JOIN — simulate with UNION:
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id
UNION ALL
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id
WHERE a.id IS NULL;Common Pitfalls
- FULL OUTER JOIN is not supported in MySQL — use a UNION of LEFT JOIN and RIGHT JOIN to simulate it.
- FULL OUTER JOIN can return many rows on large tables — always add WHERE conditions or use CTEs to process results.
- Interview tip: FULL OUTER JOIN is rarely used in day-to-day queries — it shines for data quality checks and ETL reconciliation.
Real-World Example
Reconciling orders between two systems:
example
sql
-- Data reconciliation: compare orders in primary vs backup system
SELECT
COALESCE(p.order_id, b.order_id) AS order_id,
p.amount AS primary_amount,
b.amount AS backup_amount,
CASE
WHEN p.order_id IS NULL THEN 'Missing in Primary'
WHEN b.order_id IS NULL THEN 'Missing in Backup'
WHEN p.amount != b.amount THEN 'Amount Mismatch'
ELSE 'Synchronized'
END AS reconciliation_status
FROM primary_orders p
FULL OUTER JOIN backup_orders b ON p.order_id = b.order_id
WHERE p.order_id IS NULL
OR b.order_id IS NULL
OR p.amount != b.amount
ORDER BY reconciliation_status, order_id;