Why does NOT IN return no rows when the subquery contains a NULL?

Why

A query meant to list samples that have not been sequenced might read WHERE sample_id NOT IN (SELECT sample_id FROM sequencing_runs). It works for months, then one run is loaded with a blank sample_id and the query suddenly returns nothing. The PostgreSQL documentation explains why.

NOT IN is true only if every subquery row is unequal. If all the comparisons are unequal or null, with at least one null, the result is null. Since S-101 = NULL is unknown, the database cannot say S-101 is absent from the list, and WHERE keeps only rows where the condition is true.

No error is raised, and a report of zero pending samples is exactly the kind of result that gets believed. NOT EXISTS (SELECT 1 FROM sequencing_runs r WHERE r.sample_id = s.sample_id) does not have this problem, because a NULL in the runs table simply never matches. Adding WHERE sample_id IS NOT NULL inside the NOT IN subquery also works, but must be remembered every time.

The deeper fix is upstream: a run with no sample identifier is a data quality failure, and a NOT NULL constraint would have refused it.