Why Does NULL Break Your WHERE Clause in SQL?
WHERE silently drops NULL rows because comparisons with NULL are unknown, not false. See the NOT IN query that returns zero rows for everyone.
Five employees just vanished from your WHERE clause
Count every employee, then split them by whether they report to manager 1.
20 total, 3 reporting to manager 1, 12 not reporting to manager 1. 3 + 12 is 15,
not 20. Five employees are missing from both buckets, and neither query raised
an error to tell you. They're the five employees whose manager_id is NULL:
nobody at the top of an org chart has a manager, so there's nothing for = or
<> to compare against. SQL doesn't guess; it marks the comparison unknown,
and WHERE only keeps rows where the condition is TRUE. Unknown isn't true,
so the row is dropped, silently, for both the = query and the <> query at
once.
NULL isn't a value, it's UNKNOWN, and WHERE only keeps TRUE
Run the comparison as a column instead of a filter and you can see the third state directly.
Most rows show true or false. The five rows with a NULL manager show
NULL in that column too, because manager_id = 1 couldn't resolve to either
answer. That third state is what SQL calls unknown, and every operator
built on =, including <>, <, >, and BETWEEN, produces unknown the
same way whenever either side is NULL. WHERE, HAVING, and CASE WHEN
all treat unknown exactly like false: the row is excluded, the branch is
skipped. The only predicates that ever return a real true or false for a
NULL input are IS NULL and IS NOT NULL, which is why they're the one
correct way to test for it.
= NULL and != NULL always return nothing
WHERE manager_id = NULL matches zero rows, even the ones where
manager_id genuinely is NULL. It isn't a typo for IS NULL, it's a
comparison that can never be true, because SQL never claims to know whether
one unknown value equals another.
Getting the NULL rows back
If you actually want "everyone except manager 1's direct reports, including
the ones with no manager," say so explicitly with OR:
17 now: the 12 from before plus the 5 with no manager, which is 20 minus the
3 who do report to manager 1. That's the number that was missing. This
pattern, repeating the column with IS NULL tacked on, is the fix for any
<>, NOT IN (fixed list), or negative filter once you know NULL might be
sitting in that column.
The edge case that wipes out the entire result
Everything above only drops some rows. There's a version of this that
drops all of them, and it's the one bug report every production SQL codebase
eventually files. Say you want every employee who doesn't manage anyone:
their id should not show up as someone else's manager_id.
Zero rows. Not "fewer than expected," zero, for a table of 20 people where
14 of them obviously manage nobody. The subquery SELECT manager_id FROM employees returns a NULL alongside the real manager ids, because five
employees have no manager of their own. NOT IN expands to a chain of <>
comparisons joined by AND: id <> 1 AND id <> 2 AND id <> 5 AND id <> NULL AND .... The moment any link in that AND chain is unknown, the whole
chain can be unknown too, and it can never be definitely TRUE. One NULL
anywhere in the list poisons every row the list is checked against, not just
the rows that would have matched it.
This is the opposite of IN
IN builds the same chain with OR instead of AND. TRUE OR unknown is
still TRUE, so a NULL in the list is harmless for plain IN. It's
specifically the negation, NOT IN, where the same NULL turns from
ignorable into catastrophic. Plenty of write-ups on this topic cover IN
and NOT IN as if they fail the same way; they don't.
NOT EXISTS doesn't have this problem
The safe rewrite replaces the list comparison with a row-by-row check, which
never has an "unknown poisons everything" step because there's no AND
chain to poison.
14 rows, which is the correct count: 20 employees minus the 6 who actually
appear as someone's manager. NOT EXISTS asks, for each employee in turn,
"does any row make this inner condition true?" A NULL manager_id in the
inner table just fails to match, the same as any other non-match; it never
gets combined into one giant chain, so it can't take the whole query down
with it. If you can't rewrite as NOT EXISTS for some reason, the other
working fix is to filter the subquery itself: WHERE id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL) also returns the
correct 14 rows, because there's no NULL left in the list to poison the
chain. Either fix works; NOT EXISTS is the one that keeps working if
someone adds a nullable column later and forgets to update the filter.
AND and OR don't always inherit unknown
Unknown doesn't necessarily win just because it shows up. FALSE AND unknown is still FALSE, and TRUE OR unknown is still TRUE, because
the other side already decided the answer. It only spreads when nothing
else in the expression is strong enough to override it, which is exactly
what happens inside a long AND chain built from NOT IN.
Where to go next
The Understanding NULL lesson covers the basics this post
assumes: why NULL isn't zero or an empty string, and why IS NULL exists
at all. The NOT EXISTS anti-join example
walks through the "find rows in one table that aren't in another" pattern in
more depth than the single query above. If you'd rather replace a NULL
with a default than filter it out, for example showing "No Manager" instead
of a blank cell, the COALESCE reference page covers
that. The practice problems are worth trying next if you want to
find out whether you'd have caught the zero-row NOT IN bug before it shipped.
Cite this resource
SQLSimplified. "Why Does NULL Break Your WHERE Clause in SQL?". Available at: https://sqlsimplified.online/blog/why-null-breaks-your-where-clause-in-sql