How do you use a FULL OUTER JOIN to detect missing or mismatched rows between two tables?
A FULL OUTER JOIN combined with IS NULL checks on each side isolates rows that exist in only one table, making it ideal for data reconciliation, pipeline audits, and finding discrepancies between a source and a target table.
How to think about it
This question shows up in data engineering interviews because reconciliation — comparing two tables and finding what differs — is a real daily task. The FULL OUTER JOIN pattern answers three questions in one pass: what matches, what’s only in the left table, and what’s only in the right table.
The mental model first
A regular INNER JOIN only shows rows that match on both sides. A LEFT JOIN shows all left rows plus matches from the right. A FULL OUTER JOIN shows everything from both sides, with NULL on the side that has no match.
Building the reconciliation query
Step through the logic
Row 101 matches and amounts agree — status is match. Row 303 matches but the amounts differ (800 vs 750) — amount_mismatch. Row 202 has no target counterpart — missing_in_target. Row 505 has no source counterpart — missing_in_source.
Why COALESCE on the key matters
Because either side can be NULL on a non-matching row, src.id alone will show NULL wherever a row exists only in the target. COALESCE(src.id, tgt.id) always gives you a usable identifier.
Practical tip for large tables
For tables with millions of rows, materialize the FULL OUTER JOIN into a staging table first, then query the staging table for each discrepancy type. Repeated full scans of a billion-row join are expensive — stage it once and run lightweight queries against the result.