FULL OUTER JOIN returns all records from both tables, with NULL where there's no match.

Basic FULL OUTER JOIN

SELECT customers.name, orders.id

FROM customers

FULL OUTER JOIN orders ON customers.id = orders.customer_id;

MySQL Alternative (No FULL OUTER JOIN)

SELECT customers.name, orders.id

FROM customers

LEFT JOIN orders ON customers.id = orders.customer_id

UNION

SELECT customers.name, orders.id

FROM customers

RIGHT JOIN orders ON customers.id = orders.customer_id;

Find All Unmatched Records

SELECT c.name, o.id

FROM customers c

FULL OUTER JOIN orders o ON c.id = o.customer_id

WHERE c.id IS NULL OR o.id IS NULL;

Data Reconciliation

SELECT

COALESCE(a.id, b.id) as id,

a.value as system_a,

b.value as system_b

FROM system_a a

FULL OUTER JOIN system_b b ON a.id = b.id

WHERE a.value != b.value OR a.value IS NULL OR b.value IS NULL;

Generate FULL JOIN Queries

AI2sql handles complex JOIN scenarios.