How do you use a FULL OUTER JOIN to detect missing or mismatched rows between two tables?

How to think about it

This one shows up in data-engineering interviews because reconciliation — comparing two tables and finding what differs — is a real daily task. A FULL OUTER JOIN answers three questions in a single pass: what matches, what’s only on the left, and what’s only on the right.

The mental model: an INNER JOIN shows only rows that match on both sides; a LEFT JOIN shows all left rows plus right matches; a FULL OUTER JOIN shows everything from both sides, with NULL on whichever side has no match. So a row’s category falls straight out of which key is NULL:

src.id NOT NULL, tgt.id NOT NULL  →  matched (now compare the value columns)
src.id NOT NULL, tgt.id IS NULL   →  missing in target
src.id IS NULL,  tgt.id NOT NULL  →  missing in source

A worked example

One pass classifies every id as matched, mismatched, or missing on a side:

SELECT COALESCE(src.id, tgt.id) AS id,
       src.amount AS source_amount,
       tgt.amount AS target_amount,
       CASE
           WHEN src.id IS NULL           THEN 'missing_in_source'
           WHEN tgt.id IS NULL           THEN 'missing_in_target'
           WHEN src.amount != tgt.amount THEN 'amount_mismatch'
           ELSE 'match'
       END AS status
FROM source_payments src
FULL OUTER JOIN target_payments tgt ON src.id = tgt.id
ORDER BY COALESCE(src.id, tgt.id);

id	source_amount	target_amount	status
101	500	500	match
202	300	NULL	missing_in_target
303	800	750	amount_mismatch
404	150	NULL	missing_in_target
505	NULL	200	missing_in_source

Every reconciliation outcome in one result: 101 agrees on both sides; 303 exists in both but the amounts disagree (800 vs 750); 202 and 404 are in the source with no target row; 505 is in the target with no source row. The CASE simply reads the NULL pattern of the two keys to label each.

Why COALESCE on the key matters

Because either side can be NULL on a non-matching row, src.id alone goes blank wherever a row exists only in the target (505, above). COALESCE(src.id, tgt.id) always hands you a usable identifier, whichever side the row came from.

At scale

For million-row tables, materialise the FULL OUTER JOIN into a staging table once, then run a lightweight query per discrepancy type against it. Repeatedly full-scanning a billion-row join is expensive — stage it, then ask your questions of the result.