The NOT IN trap: why your SQL query returns zero rows

A query that returns an empty result is usually a wrong WHERE clause. But there is one class of empty result that confuses even experienced people, because every row it filters on looks correct: NOT IN with a NULL in the subquery.

The failure

Two tables. Customers, and the orders they placed:

CREATE TABLE customers (id int, name text);
CREATE TABLE orders (customer_id int);

INSERT INTO customers VALUES (1,'Ada'), (2,'Grace'), (3,'Linus');
INSERT INTO orders VALUES (1), (NULL);

Customer 3 has never ordered. So this should return Linus:

SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

It returns nothing. Not Linus, not anybody. Zero rows.

Why

NOT IN is shorthand for a chain of AND-ed comparisons:

id <> 1 AND id <> NULL      -- for Linus: 3 <> 1 AND 3 <> NULL

And 3 <> NULL does not evaluate to true. It evaluates to UNKNOWN, because NULL means "unknown value" — and you cannot assert that 3 is different from a value you do not know.

SQL uses three-valued logic: every comparison is TRUE, FALSE, or UNKNOWN.

Expression	Result
3 = 1	FALSE
3 <> 1	TRUE
3 = NULL	UNKNOWN
3 <> NULL	UNKNOWN
NULL = NULL	UNKNOWN

WHERE keeps a row only when the condition is TRUE. TRUE AND UNKNOWN is UNKNOWN. So the single NULL in the orders table poisons the comparison for every customer, and the whole result collapses to empty.

Note that NULL = NULL is UNKNOWN too. NULL is not equal to itself — it is not a value, it is the absence of one.

Three ways to fix it

Fix 1 — filter the NULLs out of the subquery. Cheapest, and usually the right answer when the NULL is meaningless data:

SELECT name
FROM customers
WHERE id NOT IN (
  SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

Fix 2 — use NOT EXISTS. This is the version I reach for by default, because it is null-safe by construction: EXISTS returns a plain boolean about whether rows matched, and the comparison inside is evaluated per row rather than collapsed into a set:

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Fix 3 — LEFT JOIN with an IS NULL check. The classic anti-join:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.customer_id IS NULL;

All three return Linus. Pick by readability and by what your optimizer likes — modern planners usually turn all three into the same anti-join.

The same trap in plain comparisons

Once you internalise that NULL is UNKNOWN, other surprises stop being surprising:

SELECT * FROM orders WHERE customer_id <> 1;   -- rows with NULL are excluded
SELECT * FROM orders WHERE customer_id = NULL; -- always empty
SELECT * FROM orders WHERE customer_id IS NULL; -- this is the one that works

COUNT(*) counts rows; COUNT(column) counts non-NULL values. For the orders above, COUNT(*) is 2 and COUNT(customer_id) is 1. That difference has broken more dashboards than any other SQL subtlety — an average computed as SUM(x)/COUNT(*) silently treats missing values as zeros.

What to take away

• NOT IN against a subquery that can produce NULL returns zero rows. Always.
• IS NULL / IS NOT NULL are the only operators that test for NULL directly.
• Prefer NOT EXISTS for anti-joins; it removes an entire category of bug.
• Aggregates ignore NULL, which changes denominators.

If you are porting queries between engines, note that COALESCE is standard while IFNULL (MySQL/SQLite), NVL (Oracle), and ISNULL (SQL Server) are dialect-specific — COALESCE works everywhere.

I wrote up the full set of NULL behaviours, including truth tables, NULLIF, and how each engine sorts NULLs, at SQL NULL handling: common pitfalls and fixes. And if you want to eyeball a query's structure before running it, the online SQL formatter is client-side — the text never leaves the browser.