Why Does Your NOT IN Return Zero Rows? SQL's Three-Valued Logic and the NULL Trap
NULL in SQL is not a value — it is a marker meaning "unknown". Every comparison against it yields UNKNOWN, and WHERE only keeps rows that evaluate to TRUE, so UNKNOWN is discarded exactly like FALSE. The most serious consequence lands on NOT IN: a single NULL anywhere in the subquery makes the entire query return zero rows, even when the data plainly exists. I ran it on PostgreSQL 16: with the same intent, NOT IN returns 0 rows while NOT EXISTS returns 3. No error, no warning — the query just quietly returns the wrong answer.
This is not the kind of bug that crashes an application. It just makes a report short by a few numbers, makes a list screen empty, makes a campaign miss some customers — and leaves nothing behind in the logs.
This post drills into one detail from my 30-day SQL series, specifically the lessons on operators and expressions and subqueries. Every number below is real output from PostgreSQL 16.11.