🗄️ Your NOT IN Subquery Returns Nothing at All – Because of One NULL Value
A `NOT IN` subquery that should exclude a handful of specific IDs instead returns ZERO rows entirely, not just the wrong rows – and the cause is almost always a single NULL value hiding in the subquery’s result set, because SQL’s three-valued logic makes `NOT IN` against a list containing NULL evaluate to UNKNOWN for every single row, which the WHERE clause treats the same as false.
🔎 The Problem
SELECT * FROM Customers
WHERE CustomerId NOT IN (
SELECT CustomerId FROM BlacklistedCustomers
);
-- If BlacklistedCustomers.CustomerId contains even ONE NULL row (an
-- incomplete record, a bad data entry), the entire query returns ZERO
-- rows - not "all customers except the blacklisted ones", but nothing
-- at all. This is because `x NOT IN (1, 2, NULL)` evaluates to
-- `x <> 1 AND x <> 2 AND x <> NULL` - and `x <> NULL` is UNKNOWN for
-- every value of x, which poisons the entire AND chain to UNKNOWN,
-- and UNKNOWN is treated as "don'"'"'t include this row" by WHERE.
✅ Fix: Filter Out NULLs, or Use NOT EXISTS Instead
- The quick fix: explicitly exclude NULLs from the subquery – `WHERE CustomerId NOT IN (SELECT CustomerId FROM BlacklistedCustomers WHERE CustomerId IS NOT NULL)` – which resolves the immediate symptom with a one-line change.
- The more robust fix, recommended as a general habit: use `NOT EXISTS` instead of `NOT IN` for this kind of subquery – `WHERE NOT EXISTS (SELECT 1 FROM BlacklistedCustomers b WHERE b.CustomerId = Customers.CustomerId)` – which is immune to this NULL trap entirely, since it never evaluates an equality/inequality against NULL the same way.
⚠️ Why This Is a Trap Worth Remembering Permanently
- The subquery returning a NULL is often completely unrelated to the actual business logic – a bad data entry, an unrelated bug elsewhere that let a NULL slip into a column that should never have one – which makes the resulting empty result set from the OUTER query feel disconnected from its actual root cause.
- This exact trap is common enough that many style guides recommend NEVER using `NOT IN` with a subquery, specifically because there’s no way to guarantee the subquery’s result set stays NULL-free forever as the schema or data evolves – `NOT EXISTS` doesn’t have this failure mode at all.
NOT IN doesn’t fail loudly when a NULL sneaks into its list – it fails by going quiet, returning nothing, and leaving no error to point at the real cause.
