Why NOT IN returns nothing when the list contains NULL

This query should list customers who have never referred anyone, meaning their id never appears in referred_by:

SELECT id, name
FROM customers
WHERE id NOT IN (SELECT referred_by FROM customers);

Zero rows. That's clearly wrong: not all 40 customers referred someone.

The logic

referred_by is NULL for customers nobody referred, so the subquery's list contains NULL.

id NOT IN (a, b, NULL) expands to id <> a AND id <> b AND id <> NULL. Comparing anything to NULL gives unknown. For a row to pass, every part of that AND must be true, and the last part never is. The best possible outcome is unknown, and WHERE throws away unknown.

So a single NULL in the list removes every row. IN doesn't have this problem, because OR only needs one true branch.

Fix: NOT EXISTS

NOT EXISTS asks whether a matching row exists, and that question has only two answers.

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM customers r WHERE r.referred_by = c.id
)
ORDER BY c.id
LIMIT 5;

Filtering the NULLs out of the subquery also works, and it's fine if you're careful to do it every time:

SELECT COUNT(*) AS never_referred_anyone
FROM customers
WHERE id NOT IN (SELECT referred_by FROM customers WHERE referred_by IS NOT NULL);

The same trap with not-equals

NULL affects simple filters too. Seven customers in this database have no city on record. Filtering out Tampa quietly removes them as well, even though none of them is in Tampa:

SELECT COUNT(*) AS not_tampa FROM customers WHERE city <> 'Tampa';

SELECT COUNT(*) AS not_tampa_including_unknown
FROM customers
WHERE city <> 'Tampa' OR city IS NULL;

Whenever a column can be NULL, decide what a missing value should mean for your question, and write that down in the query.