How do you reconcile two tables with a FULL OUTER JOIN and label rows missing on either side or mismatched?

## The shape of a reconciliation query Reconciliation asks three questions about two data sets that are supposed to agree: what is here but not there, what is there but not here, and what is in both but different. A `FULL OUTER JOIN` answers all three in a single pass, because it is the only join type that drops nothing. ```sql SELECT COALESCE(s.order_id, t.order_id) AS order_id, s.amount AS source_amount, t.amount AS target_amount, CASE WHEN t.order_id IS NULL THEN 'missing_in_target' WHEN s.order_id IS NULL THEN 'missing_in_source' WHEN s.amount IS DISTINCT FROM t.amount THEN 'amount_differs' ELSE 'match' END AS status FROM source_orders s FULL OUTER JOIN target_orders t ON s.order_id = t.order_id; ``` Three design choices carry the query. ## Choice 1 — join on a stable business key The `ON` predicate defines what "the same row" means. A surrogate key generated independently in each system is useless here; the key must be something both sides derive from the same fact — an order number, an external reference, a natural composite. If the key is not unique on either side, the matched bucket multiplies and your "differences" report inflates, so establish uniqueness first (or aggregate to a level where it holds). Getting the grain right is the part that goes wrong in practice, not the join syntax. ## Choice 2 — classify with the raw key columns, not the coalesced one `COALESCE` deliberately erases which side a value came from, so it is right for the output identifier and wrong for the classification. The `CASE` therefore tests `s.order_id` and `t.order_id` individually. Order matters: the two "missing" branches must come before the comparison branch, because for an unmatched row the payload columns are NULL-extended and comparing them would produce a meaningless verdict. ## Choice 3 — compare values NULL-safely This is where reconciliation queries quietly lie. If `s.amount` is 100 and `t.amount` is NULL, then `s.amount <> t.amount` evaluates to unknown, not true, so a `WHEN s.amount <> t.amount` branch does not fire and the row is reported as a match. The standard's NULL-safe inequality is `IS DISTINCT FROM`, which returns true here and false when both sides are NULL. Not every engine implements it; the portable expansion is: ```sql (s.amount <> t.amount) OR (s.amount IS NULL AND t.amount IS NOT NULL) OR (s.amount IS NOT NULL AND t.amount IS NULL) ``` Repeat that per compared column, or `COALESCE` both sides to a sentinel value that cannot occur in the data — a technique that works but is fragile, since choosing a sentinel that the data can produce reintroduces the bug. ## Reporting only the discrepancies A reconciliation that returns every row is fine for a small data set and useless for a large one. Filter to the three interesting cases: ```sql WHERE s.order_id IS NULL OR t.order_id IS NULL OR s.amount IS DISTINCT FROM t.amount ``` Or wrap the query and filter on the computed status — often clearer, and it guarantees the filter and the label can never disagree: ```sql WITH diff AS ( /* the query above */ ) SELECT * FROM diff WHERE status <> 'match'; ``` A summary count by status is usually the first thing an operator wants: group the wrapped query by `status` and count, which turns the reconciliation into a one-line health signal. ## Comparing many columns With a wide row, a per-column `CASE` chain becomes unmanageable. Two portable options: build a status list of the columns that differ using conditional expressions, or compare a digest of the concatenated payload — with the caveat that concatenation must handle NULLs and separators carefully, or two different rows can produce the same string. When the interviewer pushes on this, the honest answer is that column-level detail is what makes a reconciliation actionable, so a mechanical per-column comparison generated from the schema usually beats a single opaque digest. ## Portability note If the engine has no `FULL OUTER JOIN`, the same reconciliation is expressible as a `LEFT JOIN` branch plus an anti-joined branch combined with `UNION ALL`, with the `CASE` logic repeated in both branches — or, since the three status buckets are already disjoint, as three `UNION ALL` branches each emitting a constant status literal. ## What good looks like A solid answer names the join type and says why (nothing is dropped), rebuilds the key with `COALESCE`, classifies from the raw key columns in the right branch order, and — the detail that separates candidates — insists on NULL-safe value comparison, because that is the failure that makes a reconciliation report clean while the data is not.