How do you find staging rows absent from a target table when the match key spans two columns?

## The task Reconciliation questions — which staged rows have not been loaded, which line items are missing from the archive, which of yesterday's records never arrived — are anti-joins on a **composite key**. The single-column patterns still apply, but the three spellings stop being equally practical, and the interviewer is checking whether you know which one degrades. ## NOT EXISTS: the form that scales ```sql SELECT s.order_id, s.line_no, s.qty FROM staging_lines s WHERE NOT EXISTS ( SELECT 1 FROM order_lines t WHERE t.order_id = s.order_id AND t.line_no = s.line_no ); ``` Everything about the single-column version carries over unchanged. The subquery is correlated on two columns instead of one; `EXISTS` still yields TRUE or FALSE with no third state; the result keeps one row per unmatched staging row, and every staging column stays available for projection so you can output the rows themselves rather than just their keys. Adding a third key column means adding a third ANDed line. No other form is this stable under change. ## LEFT JOIN with IS NULL: correct if you are careful ```sql SELECT s.order_id, s.line_no, s.qty FROM staging_lines s LEFT JOIN order_lines t ON t.order_id = s.order_id AND t.line_no = s.line_no WHERE t.order_id IS NULL; ``` Both equalities are part of the join condition. Only after the join do you test for NULL-extension, and you test a **key column of the target**, because a matched row cannot have a NULL there. Two mistakes are common in the composite case: moving the second equality to `WHERE`, which discards NULL-extended rows and silently returns nothing; and testing a nullable payload column of the target, which reports genuinely present rows as missing. ## NOT IN with a row-value constructor: portable in theory Standard SQL allows comparing a row value against a subquery's rows: ```sql SELECT s.order_id, s.line_no FROM staging_lines s WHERE (s.order_id, s.line_no) NOT IN ( SELECT t.order_id, t.line_no FROM order_lines t ); ``` In practice, engines differ on whether row-value constructors are accepted in `IN`, so this is the least portable spelling — check your engine's documentation before relying on it. It also inherits `NOT IN`'s NULL behaviour: a NULL in either projected column of the subquery can make the predicate never TRUE, so the query returns nothing at all. The failure is silent, which is the worst property a reconciliation query can have. ## Nullable key columns Composite keys in staging data are often not declared `NOT NULL`. Equality comparison never matches NULL to NULL, so any staging row whose key contains a NULL will be reported as missing even when a byte-identical row exists in the target. Decide first what a NULL means in your key: if it is genuinely "no value" and should match another absence, the comparison must be NULL-safe rather than a plain `=`; if it is "unknown", then "missing" is arguably the right answer and you should say so explicitly. Either way, state the decision — a reconciliation report that quietly treats unknown as missing produces phantom discrepancies every run. ## Practical framing A good answer sequences it: (1) name the pattern — this is an anti-join on a composite key; (2) give `NOT EXISTS` as the default and show that it grows by one predicate per column; (3) offer the `LEFT JOIN` form with its two conditions, both equalities in `ON` and `IS NULL` on a target key column; (4) flag the row-constructor `NOT IN` as portability- and NULL-sensitive; (5) raise nullable key columns as a question about the data before writing anything. Mentioning that you would run the mirror query — target rows missing from staging — completes the reconciliation, since the two directions answer different questions.