A nightly job's NOT IN filter suddenly returns zero rows after a data change — how do you diagnose and permanently fix it?

## The symptom A reconciliation or cleanup job that has run correctly for months processes zero rows. Nothing failed; the query returned an empty result and the job dutifully did nothing. The query looks like this: ```sql SELECT o.id FROM orders o WHERE o.customer_id NOT IN (SELECT id FROM customers_active); ``` Something upstream changed — a column became nullable, a view started emitting NULLs, an import wrote a NULL id, a `LEFT JOIN` inside the source view began NULL-extending. Now the subquery yields at least one NULL and the filter matches nothing. ## Diagnose in one query ```sql SELECT COUNT(*) AS null_ids FROM customers_active WHERE id IS NULL; ``` If that count is greater than zero, you have your cause and you do not need to look further. It is worth making this the *first* check whenever an anti-filter returns empty, before profiling anything, because it is a one-line test with a definitive answer. ## The mechanism, briefly `x NOT IN (S)` is defined as `x <> ALL (S)`: TRUE only when `x <> v` is TRUE for every v in S. Comparing to NULL yields UNKNOWN rather than TRUE, and a universal claim with an undecided conjunct is undecided. `WHERE` keeps only TRUE, so every candidate is dropped — the matching ones because the predicate is FALSE, the non-matching ones because it is UNKNOWN. The result is empty rather than merely wrong, which is the tell. ## The fix ladder **1. Fix the data model.** If `customers_active.id` should never be NULL, declare it `NOT NULL` (or fix the view that manufactures the NULL). This makes the predicate unambiguous at the source and protects every other query that reads the same column. It is the only fix that is impossible to lose in a later refactor of the query. **2. Rewrite as `NOT EXISTS`.** ```sql SELECT o.id FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM customers_active c WHERE c.id = o.customer_id ); ``` Inside the subquery, a NULL id makes `c.id = o.customer_id` UNKNOWN, so that row simply is not a match. EXISTS then answers a definite yes or no about row count, so `NOT EXISTS` can never be UNKNOWN. This is correct whether or not the column is nullable, which is why it is the right default habit for anti-filters even in schemas you believe are clean. **3. Filter inside the subquery.** ```sql ... NOT IN (SELECT id FROM customers_active WHERE id IS NOT NULL) ``` This is semantically correct and sometimes the smallest safe patch during an incident. Its weakness is durability: the `IS NOT NULL` looks like an incidental filter, carries no explanation, and is exactly the sort of clause a later edit drops while "simplifying". If you use it, comment why. Note what does **not** work: coalescing the outer column (`COALESCE(o.customer_id, -1) NOT IN ...`) changes nothing, because the poisoning NULLs are on the subquery side. Neither does `DISTINCT` in the subquery, nor swapping to `<> ANY` — which is a different predicate entirely and is TRUE almost always. ## Close the loop After the fix, add a fixture row with a NULL in the subquery column to the test that covers this job. The original test passed because the test data was clean; the class of bug is "correct on clean data, silently empty on real data", so only a NULL-carrying fixture reproduces it. Where the job's output is an action rather than a report, a sanity assertion — "this job should never process exactly zero rows two nights running" — turns the silent failure into an alert. ## Prevention as a rule Most teams that have hit this once adopt a standing convention: anti-filters are written with `NOT EXISTS`, and `NOT IN` over a subquery is flagged in review unless the column is provably `NOT NULL`. The rule is cheap because the rewrite is mechanical and reads at least as well, and it removes a defect class that produces no error message at all. In an interview, this is the part that separates "I know the trap" from "I have operated systems that hit it": say how you would notice, how you would confirm, what you would change in the schema, and what would have caught it earlier.