NOT IN returned zero rows. It wasn't your data — it was one NULL.

Originally published at nlqdb.com/blog

“Which customers never placed an order?” is a question you ask constantly — products never sold, users with no login this month, invoices with no payment. It’s a set difference, and the obvious query is a quiet trap:

SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);   -- returns nothing. why?

Enter fullscreen mode Exit fullscreen mode

If a single customer_id in that subquery is NULL, you get zero rows — no error, no warning. Here’s why: NOT IN (a, b, NULL) expands to id <> a AND id <> b AND id <> NULL. That last comparison is never true — comparing anything to NULL is unknown — so the whole AND chain can never be true, and every row is rejected. One NULL in the inner table silently empties your result.

The two shapes that actually work

-- LEFT JOIN ... IS NULL: keep the customers that found no matching order
SELECT c.* FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- NOT EXISTS: a correlated anti-join, NULL-safe by construction
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Enter fullscreen mode Exit fullscreen mode

Both return the same rows for a plain anti-join. NOT EXISTS stops at the first match and never trips over NULLs. The LEFT JOIN ... IS NULL form is just as correct — but if the join key isn’t unique it can multiply rows before the filter, so know your grain. What neither of them does is silently lie to you the way NOT IN does.

The rule worth keeping: reach for NOT EXISTS (or LEFT JOIN ... IS NULL) for “rows with no match,” and treat NOT IN (subquery) as a smell unless you’re certain the subquery is NULL-free.

If you’d rather not re-derive which shape is safe every time: nlqdb takes “customers who never placed an order” in English, compiles the NULL-safe anti-join, runs it read-only, and shows the SQL so you can confirm it isn’t a NOT IN. Honest limit — it owns the Postgres it answers; bring-your-own-Postgres is signed-in only, not the public embed.