SQL NOT IN returns no rows when the subquery has a NULL

This query looks like it should return every user who has not been banned:

SELECT id FROM users
WHERE id NOT IN (SELECT user_id FROM bans);

It works for months. Then one row lands in bans with a NULL in user_id, and the query returns nothing. Not fewer rows. Zero. No error, no warning, and the flipped version with IN still looks perfectly healthy.

That is the whole gotcha behind SQL NOT IN returning no rows when the subquery contains a NULL. It is not a Postgres bug, and MySQL, SQL Server and SQLite do the same thing. It falls straight out of how SQL compares against NULL.

Reproduce it in four lines

CREATE TEMP TABLE users (id int);
CREATE TEMP TABLE bans  (user_id int);
INSERT INTO users VALUES (1), (2), (3);
INSERT INTO bans  VALUES (2), (NULL);

SELECT id FROM users WHERE id NOT IN (SELECT user_id FROM bans);
-- (0 rows)

SELECT id FROM users WHERE id IN (SELECT user_id FROM bans);
-- 2

Users 1 and 3 are obviously not banned. NOT IN still refuses to say so.

Why NOT IN with a NULL returns nothing

x NOT IN (a, b, c) is shorthand. The database expands it into a chain of inequalities joined with AND:

id NOT IN (2, NULL)
-- means
id <> 2 AND id <> NULL

Any comparison with NULL using = or <> does not produce true or false. It produces NULL, which SQL treats as "unknown". So for user 1:

• 1 <> 2 is true
• 1 <> NULL is unknown
• true AND unknown is unknown

A WHERE clause only keeps rows where the condition is true. Unknown is not true, so the row is dropped. The same happens for every row that is not in the list, which is exactly the set of rows you wanted.

You can see the three-valued logic directly:

SELECT 1 NOT IN (2, NULL) AS a,   -- NULL
       2 NOT IN (2, NULL) AS b,   -- false
       1 <> NULL          AS c;   -- NULL

The only result NOT IN can ever give you against a list containing NULL is false or unknown. It can never be true.

IN gets away with it because it expands with OR instead: id = 2 OR id = NULL. For user 2 that is true OR unknown, which is true, so matches still come back. The missing rows only show up on the negated side, which is why this bug hides so well.

The same rule applies to arrays in Postgres. id <> ALL (ARRAY[2, NULL]) is just NOT IN spelled differently, and it also comes back NULL for every non-matching value.

Where the NULL sneaks in

Nobody writes NOT IN (2, NULL) on purpose. The NULL comes from the subquery, usually from a column you assumed was always filled:

• A nullable foreign key, like bans.user_id on a ban against an IP address or an email rather than an account.
• A LEFT JOIN inside the subquery, which produces NULLs for every unmatched row.
• An ON DELETE SET NULL constraint, which turns rows into NULL references long after the query was written.
• A new code path that inserts a row before the ID is known.

The query is correct on the day you ship it, and breaks on the day the data changes. Tests with tidy fixtures will never catch it.

Fix: use NOT EXISTS

The fix to remember is NOT EXISTS. It asks a different question: "is there any row in bans that matches this user?" A NULL in bans.user_id never equals anything, so it simply never matches, and it cannot poison the result.

SELECT id FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM bans b WHERE b.user_id = u.id
);
-- 1
-- 3

It is usually the faster option too. Postgres can plan NOT EXISTS as an anti join, which works with hash, merge or nested loop strategies:

Hash Anti Join
  Hash Cond: (u.id = b.user_id)
  ->  Seq Scan on users u
  ->  Hash
        ->  Seq Scan on bans b

For NOT IN against a subquery, the planner has to preserve the NULL semantics above, so it generally cannot use an anti join. You will typically see a filter on a hashed subplan instead. That is fine while the subquery fits in memory; when it does not, it can fall back to rechecking the subquery for each outer row, which is where NOT IN queries get dramatically slow on large tables. Check EXPLAIN on your own version and data before assuming either way.

A LEFT JOIN with an IS NULL check gives the same result as NOT EXISTS and is common in older codebases:

SELECT u.id FROM users u
LEFT JOIN bans b ON b.user_id = u.id
WHERE b.user_id IS NULL;

It works, but NOT EXISTS says what you mean, and the next person to read the query does not have to work out that the IS NULL check is doing the anti-join.

If you keep NOT IN, filter the NULLs

Sometimes NOT IN reads better, or an ORM generates it. In that case, exclude NULL explicitly inside the subquery:

SELECT id FROM users
WHERE id NOT IN (
  SELECT user_id FROM bans WHERE user_id IS NOT NULL
);
-- 1
-- 3

This is correct, but it relies on everyone who edits the query later remembering why that line is there. A NOT NULL constraint on the column is stronger, if the data model allows it.

There is one difference worth knowing between the two fixes. If the outer column can be NULL itself, they disagree. A user row with a NULL id passes NOT EXISTS, because no ban matches it. The same row fails the filtered NOT IN, because NULL NOT IN (...) is unknown. Decide which behavior you want rather than letting the syntax decide.

A short rule

• Treat NOT IN (subquery) as a smell unless the column is declared NOT NULL.
• Reach for NOT EXISTS by default for anti-joins.
• NOT IN with a literal list you control, like status NOT IN ('draft', 'archived'), is fine.
• When a negated query suddenly returns zero rows, look for a NULL before you look for anything else.