SQL NOT IN and NULL: Why Your Query Returns No Rows

Begin by displaying the NOT IN predicate for every customer, rather than placing it in WHERE immediately. The CASE expression translates the Boolean result into true, false or unknown for inspection.

Ada's ID equals 1, so she is definitely in the exclusion set and NOT IN is false. Grace and Linus do not equal 1, but comparing either ID with the NULL entry does not establish inequality. Their NOT IN results are unknown. The second SELECT then shows what happens when WHERE applies those results: no rows survive.

For this particular list, id NOT IN (1, NULL) behaves like the conjunction id <> 1 AND id <> NULL. A false comparison makes the conjunction false; a true comparison followed by an unknown comparison leaves it unknown. Neither result is true.

This is a semantics problem, not a planner bug, an empty-table problem or a reason to coerce every NULL to an arbitrary number. An empty exclusion subquery is a different case: there are no matching values to exclude. Test the real contents of the subquery, including nullability, before rewriting a larger query.

psql

SELECT c.name,
       CASE WHEN c.id NOT IN (SELECT customer_id FROM exclusions) THEN 'true'
            WHEN NOT (c.id NOT IN (SELECT customer_id FROM exclusions)) THEN 'false'
            ELSE 'unknown' END AS allowed
FROM customers c ORDER BY c.id;

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM exclusions)
ORDER BY id;

Read the inner SELECT as a question asked about the current customer: does an exclusion row have the same customer ID? For Ada, yes, so NOT EXISTS rejects her. For Grace and Linus, no, so they remain.

The NULL exclusion row cannot satisfy e.customer_id = c.id. It therefore does not create an equal match and does not poison the test for unrelated customers. The value selected by the inner query is unimportant here; SELECT 1 is a common convention because EXISTS tests whether a row exists.

Keep the correlation condition. Accidentally writing NOT EXISTS (SELECT 1 FROM exclusions) asks whether the entire exclusion table is empty, which is a completely different requirement. Duplicated exclusion IDs do not change the membership answer either: one matching row is enough.

Use a stable, appropriately constrained key for the equality comparison. This example's customer IDs are primary keys and cannot be NULL. If the outer value were nullable, NOT EXISTS would also retain an outer NULL with no equal match. That might or might not fit your business rule; add an explicit outer IS NOT NULL condition if missing outer values must be excluded.

SELECT c.name FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM exclusions e WHERE e.customer_id = c.id
)
ORDER BY c.id;

Filtering NULLs out of the inner query also returns Grace and Linus for our non-null customer IDs. This is a valid repair when the intended policy is that incomplete exclusion records should not exclude anyone.

It is not a universal equivalence between NOT IN and NOT EXISTS. An outer NULL usually remains unknown for NOT IN against a nonempty set of non-null IDs, whereas the equality-based NOT EXISTS version can retain it. If both sides have non-null keys, that distinction disappears for this ordinary membership test.

The second command counts incomplete exclusion records. Do not silently discard those records without asking why they exist. If customer_id is required by the data model, repair the data and consider a NOT NULL constraint. A foreign key alone does not make its referencing column non-null. If NULL has a deliberate business meaning, model that meaning separately instead of expecting equality to interpret it.

Avoid claiming that NOT EXISTS is always faster. PostgreSQL can choose different plans depending on the query, constraints and version. First make the two queries express the same rule, then compare plans on representative data. A faster query returning the wrong customers is still the wrong query.

SELECT name FROM customers
WHERE id NOT IN (
  SELECT customer_id FROM exclusions WHERE customer_id IS NOT NULL
)
ORDER BY id;

SELECT count(*) AS incomplete_exclusions FROM exclusions WHERE customer_id IS NULL;