A NOT IN query can return the wrong answer without telling you. It is valid SQL, it runs without an error, and it hands back a perfectly well-formed result set that happens to be empty when it should not be. No warning, no hint, nothing in the logs: just zero rows where you expected hundreds, and a database that considers it correct.

Almost always the cause is a single NULL sitting somewhere you forgot to look, combined with two keywords you have typed a thousand times: NOT IN. None of it is a Postgres bug. This is exactly what the SQL standard mandates, implemented faithfully. That is precisely what makes it so easy to walk into, and why the planner could not safely optimize around it for the better part of Postgres's history. It comes down to one if statement in the parser.

Sample schema

Nothing elaborate. A table of products, one of which has no category assigned yet, and a table of archived categories that happens to contain a NULL:

CREATE TABLE products (id int, category_id int);
INSERT INTO products VALUES (1, 10), (2, 20), (3, NULL), (4, 10);

CREATE TABLE archived (category_id int);
INSERT INTO archived VALUES (20), (NULL);

The NULL in archived is not contrived. The moment a column is nullable (and most are, by default), a NULL can find its way into any subquery you point a NOT IN at. That is the whole point: this is not an exotic data condition, it is the ordinary one.

The query that returns nothing

Here is the request you have written a hundred times: give me the products whose category is not archived.

SELECT id, category_id FROM products
WHERE category_id NOT IN (SELECT category_id FROM archived);

You expect products 1 and 4 (category 10, which is not in the archived set). What comes back is:

 id | category_id
----+-------------
(0 rows)

Every row gone. Not a subset, not an off-by-one: all of them. Drop the NULL from archived and the same query behaves:

SELECT id, category_id FROM products
WHERE category_id NOT IN (SELECT category_id FROM archived
                          WHERE category_id IS NOT NULL);

 id | category_id
----+-------------
  1 |          10
  4 |          10
(2 rows)

To understand why a single NULL empties the entire result, we have to stop thinking of NOT IN as a single thing and watch the parser take it apart.

IN is an OR, NOT IN is an AND

IN is not a primitive operator. It is shorthand that the parser rewrites into a chain of equality comparisons joined by OR:

x IN (a, b, c)
-- becomes
x = a OR x = b OR x = c

NOT IN is the logical negation of that, and by De Morgan's law negating an OR of equalities gives you an AND of inequalities:

x NOT IN (a, b, c)
-- becomes
x <> a AND x <> b AND x <> c

This is not an analogy. It is literally the expression Postgres builds, and you can read it straight off an EXPLAIN. The literal-list forms collapse into array operators whose names give the whole game away:

EXPLAIN (COSTS OFF) SELECT * FROM products WHERE category_id IN (1, 2, 3);
--  Filter: (category_id = ANY ('{1,2,3}'::integer[]))

EXPLAIN (COSTS OFF) SELECT * FROM products WHERE category_id NOT IN (1, 2, 3);
--  Filter: (category_id <> ALL ('{1,2,3}'::integer[]))

IN is = ANY: equal to any element, an OR. NOT IN is <> ALL: different from all elements, an AND.

Three-valued logic does the rest

SQL does not have two truth values, it has three: true, false, and unknown. Any comparison against NULL yields unknown, because NULL means "no value here" and you cannot ask whether an absent value is different from 20.

true AND unknown is unknown, not true. A WHERE clause keeps a row only when its predicate evaluates to true. Both false and unknown cause the row to be discarded. So product 1 is dropped. Run the same arithmetic for product 4 and you land on unknown again.

The mechanism in one sentence: the instant a single NULL enters the right-hand side, the trailing AND unknown term can never be true, so the whole NOT IN can never be true, so every row is discarded, regardless of how many million rows you have or what they contain.

NULLs on the left side too

Keeping NULLs out of the subquery is not enough. The same unknown arises from NULLs on the left: product 3 (whose category_id is NULL) evaluates to unknown AND unknown, so it is dropped even against a spotless right-hand set. IN and NOT IN are not complements: a row can fail both tests simultaneously. There is a NULL-shaped gap between them that belongs to neither.

The seam, in the source

All of this reduces to one branch in one function. Open src/backend/parser/parse_expr.c and find transformAExprIn, the routine that turns both IN and NOT IN list expressions into something the planner can chew on.

/*
 * If the operator is <>, combine with AND not OR.
 */
if (strcmp(strVal(linitial(a->name)), "<>") == 0)
    useOr = false;
else
    useOr = true;

That is the entire fork. IN arrives carrying the operator = and gets useOr = true; NOT IN arrives carrying <> and gets useOr = false. There is no special-casing of NULL anywhere in this function, and there does not need to be: the three-valued behavior is an emergent property of having chosen AND.

A grammar asymmetry: list vs. subquery

A list and a subquery are built differently. The list form is the <> ALL chain of inequalities you just saw. The subquery form is not a <> ALL at all: it becomes NOT (foo = ANY (subquery)). Different shapes, same truth table, a NULL in the comparison makes the result unknown, and unknown loses.

Why the planner won't save you

The difference between query plans is structural. A Hash Anti Join builds one hash table from the inner relation and streams the outer relation through it exactly once. A SubPlan gives the planner none of that. It cannot be reordered during the global join search, it cannot have the outer query's join clauses pushed into it to drive an index scan, and it has no multi-batch spill logic to fall back on if the hashed set turns out larger than the estimate.

The reason the planner is stuck with that shape is precisely the NULL semantics from earlier. An anti-join keeps a row when it finds no match. But NOT IN must discard a row when the comparison goes unknown, and "unknown" is not the same as "no match".

The fix is landing in PostgreSQL 19

A performance fix is landing in PostgreSQL 19 that converts NOT IN to an anti-join when it can prove that neither side of the comparison can yield NULL values. This removes a planner limitation that has stood since anti-joins were introduced, but it only applies when the planner can prove no NULL can reach the comparison. If your column is nullable, you are exactly where you have always been.

Decision matrix

¹ PostgreSQL 19 promotes this to an Anti Join when both the outer expression and the subquery output column are provably NOT NULL.

What to do instead

Default to NOT EXISTS. Make this the habit and you never hit the trap again. Same anti-join semantics and the same plan as the working NOT IN would have wanted, on every version, nullable columns or not:

SELECT id, category_id FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM archived a WHERE a.category_id = p.category_id
);

Filter the NULLs out of the subquery when you have to keep the NOT IN:

SELECT id FROM products
WHERE category_id NOT IN (
    SELECT category_id FROM archived WHERE category_id IS NOT NULL
);

Use EXCEPT for whole-set comparisons, but note that EXCEPT treats NULL as a value and will remove rows where the join column is NULL.