Key takeaway
Test the anti-join with nulls on both sides. A query returning no rows can reflect unknown predicates rather than an empty eligible archive.
Investigate the predicate before explaining the empty file
An export expected to retain unmatched jobs suddenly returns zero rows. The source still contains the jobs and the exclusion list looks almost unchanged. Before concluding that every job was excluded, inspect the query’s treatment of null. A missing key inside a NOT IN subquery can change the truth value for every otherwise unmatched candidate.
PostgreSQL documents that a scalar NOT IN result is null when no equal right-hand value exists but at least one right-hand row is null. Its WHERE clause keeps true results and discards both false and null. These two rules can produce a valid, empty query result without establishing that the source contains no eligible records.
Four synthetic candidates expose the difference
This hypothetical fixture has candidate keys 1, 2, 3 and null. The exclusion table contains 2 and null. The nulls deliberately represent unresolved identities. They are not alternate spellings of zero or empty text. Assume ordinary scalar equality and no additional filters; every result below is conditional on that stated method.
For candidate 2, an equal excluded key makes NOT IN false. For 1 and 3, the known exclusion differs but the unknown exclusion prevents a true result. The null candidate likewise has no determinate ordinary comparison here. Consequently, WHERE key NOT IN (SELECT key FROM exclusions) retains none of these four candidates. The output count alone conceals these different reasons.
| Candidate key | NOT IN result | Kept by WHERE? | Meaning in this fixture |
|---|---|---|---|
| 1 | Unknown / null | No | No equal known key; unknown exclusion remains |
| 2 | False | No | Known exclusion matches |
| 3 | Unknown / null | No | No equal known key; unknown exclusion remains |
| null | Unknown / null | No | Candidate identity unresolved |
A correlated test still needs an identity policy
For this hypothetical fixture, consider the alternative predicate c.key IS NOT NULL AND NOT EXISTS (SELECT 1 FROM exclusions e WHERE e.key = c.key). EXISTS tests whether its subquery returns a row. Under the fixture’s ordinary equality, known candidate 2 has a match; 1 and 3 do not. The explicit candidate guard prevents the null candidate from passing merely because no equality comparison returned true.
That guarded expression therefore retains keys 1 and 3 as its technical output. Removing the guard also retains the null candidate: ordinary equality to null does not identify a matching row. This is why replacing NOT IN mechanically is insufficient. Decide how unresolved candidate identities are handled and keep the failed-key count visible rather than allowing a rewrite to broaden the release silently.
The author reproduced these row sets in an in-memory SQLite fixture. The PostgreSQL behavior stated here is supported by its documentation; no PostgreSQL server was run for this article. Run the same fixture in the actual receiving engine, with its real types and transformations, before accepting the production query.
Keep an unresolved exclusion as an unresolved business fact
The query result is not permission to release keys 1 and 3. An unidentified exclusion may correspond to one of them. The fictional team can accept that its guarded query behaves as specified while holding the business eligibility decision until the exclusion owner resolves that row or approves a documented treatment. A technical absence of an equal key does not establish an affirmative right to disclose.
Filtering nulls from the exclusion table would also change the stipulated NOT IN result. Preserve that as a named transformation, with the rejected or quarantined row accounted for. Do not delete it merely to recover the expected export size. Likewise, replacing null with a sentinel can create a false match if the sentinel is a legitimate source key.
Retain the before-and-after row sets
A useful acceptance fixture records the engine/version, exact predicates, candidate and exclusion key types, null counts, expected retained keys and observed retained keys. Include a genuinely empty exclusion table as another boundary: it is a different condition from a table containing one null. Keep ordering out of the set-membership assertion unless the export also requires a separate order contract.
For this example, the completed technical finding is zero retained rows under NOT IN and keys 1 and 3 under guarded NOT EXISTS. The unresolved exclusion remains a release hold. General missing-value documentation explains the source state; this fixture demonstrates how that state propagates through a receiving predicate. Save both explanations so the next reviewer can investigate the actual failure.