NULL Is Not Zero, and NOT IN Is Not Safe

Here's a query that looks completely reasonable:

SELECT name
FROM employees
WHERE department_id NOT IN (SELECT department_id FROM archived_departments);

"Give me every employee who isn't in an archived department." It runs. It returns... nothing. Zero rows. Not "fewer rows than expected" — zero, even though you can see live employees sitting right there in the table with your own eyes.

There's no error message. No warning. The query is syntactically and semantically valid SQL, executing exactly as specified. The bug is a single NULL sitting in archived_departments.department_id, and understanding why requires understanding what NULL actually is — which is not what most people assume.

NULL doesn't mean "empty." It means "unknown."

The instinct is to treat NULL like zero, or an empty string, or false — some kind of default absence-value. It's none of those. NULL means the value is unknown. Not "known to be nothing." Unknown.

This single distinction is why SQL doesn't use two-valued logic (TRUE/FALSE) — it uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL doesn't evaluate to TRUE or FALSE. It evaluates to UNKNOWN, because you can't meaningfully compare something to a value you don't know.

SELECT NULL = NULL;      -- UNKNOWN, not TRUE
SELECT 5 = NULL;         -- UNKNOWN, not FALSE
SELECT 5 != NULL;        -- UNKNOWN, not TRUE

This isn't a database being pedantic. "Is the unknown value equal to 5?" doesn't have a yes/no answer — the honest answer is "I don't know," and that's exactly what UNKNOWN represents. A WHERE clause only keeps rows where the condition evaluates to TRUE. UNKNOWN rows get filtered out right alongside FALSE ones. That's the entire mechanism behind the bug above — and behind a handful of others you've probably already shipped without noticing.

Walking through the NOT IN bug

IN and NOT IN are shorthand for a chain of OR/AND comparisons. x IN (1, 2, NULL) expands to x = 1 OR x = 2 OR x = NULL. The x = NULL piece is always UNKNOWN, per the rule above — but OR has a shortcut: TRUE OR UNKNOWN is TRUE, because one true condition is enough. So IN with a NULL in the list usually still works fine, because a real match elsewhere in the list makes the UNKNOWN irrelevant.

NOT IN doesn't get that shortcut. x NOT IN (1, 2, NULL) expands to x != 1 AND x != 2 AND x != NULL. That last piece, x != NULL, is UNKNOWN — always, for every row, no matter what x is. And AND doesn't have a shortcut for UNKNOWN the way OR does: TRUE AND UNKNOWN is UNKNOWN, not TRUE. So the entire condition collapses to UNKNOWN for every single row, and WHERE throws all of them out. One NULL anywhere in that subquery's result silently zeroes out your entire query.

The fix is one clause:

SELECT name
FROM employees
WHERE department_id NOT IN (
  SELECT department_id FROM archived_departments WHERE department_id IS NOT NULL
);

Strip the NULL out of the subquery before it reaches NOT IN, and the bug disappears. Even better, sidestep the whole class of problem by using NOT EXISTS, which never has this failure mode regardless of what's in the subquery:

SELECT name
FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM archived_departments a WHERE a.department_id = e.department_id
);

NOT EXISTS asks a fundamentally different question — "does at least one matching row exist?" — and existence checks don't get poisoned by unrelated NULL values the way equality checks do. As a habit, prefer NOT EXISTS over NOT IN whenever the subquery's column is nullable, which in practice means: prefer it by default, and only reach for NOT IN when you've explicitly verified the column can't be NULL.

Two more places this shows up

*COUNT(column) vs COUNT().* COUNT() counts rows. COUNT(column) counts non-NULL values in that column. If phone_number is nullable and a third of your rows have no phone number, COUNT(phone_number) silently gives you two-thirds of the row count, and it's easy to misread that as "two-thirds of customers" when it's actually "customers with a phone number on file." Always know which one you're calling.

Equality filters that quietly drop rows. WHERE status != 'archived' looks like it should return every non-archived row. It won't return rows where status IS NULL, because NULL != 'archived' is UNKNOWN, not TRUE. If a status column can be unset, you almost always want WHERE status IS DISTINCT FROM 'archived' (Postgres) or an explicit OR status IS NULL.

The one-sentence version

NULL means unknown, not empty — so any direct comparison to NULL produces UNKNOWN, and UNKNOWN gets filtered out by WHERE exactly like FALSE does. Once that's automatic, NOT IN failures, undercounted COUNT(column) results, and rows that vanish from != filters all stop being mysterious — they're the same rule, showing up in three different places.

This exact NOT IN bug is one of the lessons in SQL Quest — you get handed a query that returns nothing and have to figure out why, against a real live database. Free, no signup.