Common mistake

SQL: Why NOT IN With a NULL Returns Nothing

One NULL in the list means no NOT IN test can be true, so all rows drop. Use NOT EXISTS.

The symptom

You write a NOT IN against a subquery to find rows with no match such as customers who never ordered or products never sold, and the query returns zero rows, even though you know some should qualify. The matching IN version works fine.

Why it happens

If the subquery (or list) contains a single NULL, NOT IN can never be true. x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL, and x <> NULL is unknown, never true. An AND with an unknown can never reach true, so every row is filtered out.

Positive IN checks behave differently: a matching value can still make the expression true even when the list also contains NULL. If nothing matches, however, the result can still be UNKNOWN rather than false. The especially surprising "exclude everything" behavior is the common NOT IN trap.

The fix

Use NOT EXISTS, which asks a yes/no question and is immune to nulls in the subquery. Both queries below build the same customer list from orders plus one stray NULL, and only one of them finds the customers who never ordered.

Wrong: NOT IN with a NULL in the list

with ordered_customers as (
  select customer_id from orders
  union all
  select null as customer_id
)
select customer_id
from customers
where customer_id not in (select customer_id from ordered_customers)
order by customer_id

Right: NOT EXISTS

with ordered_customers as (
  select customer_id from orders
  union all
  select null as customer_id
)
select c.customer_id
from customers as c
where not exists (
  select 1 from ordered_customers as o
  where o.customer_id = c.customer_id
)
order by c.customer_id

A LEFT JOIN … WHERE … IS NULL anti-join works too, against the same poisoned list: the NULL row matches no customer, so it never marks one as having ordered. And if you must keep NOT IN, exclude nulls in the subquery with where customer_id is not null.

Also fine: anti-join

with ordered_customers as (
  select customer_id from orders
  union all
  select null as customer_id
)
select c.customer_id
from customers as c
left join ordered_customers as o on o.customer_id = c.customer_id
where o.customer_id is null
order by c.customer_id

Rule of thumb

Prefer NOT EXISTS over NOT IN for missing-match questions. It reads clearly, handles nulls correctly, and often optimizes at least as well.