Common mistake

SQL: Why NOT IN With a NULL Returns Nothing

One NULL in the list makes every NOT IN test unknown, 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 returnszero 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. AnAND with an unknown can never reach true, so every row is filtered out.

IN doesn't have this problem: it only needs one match to be true, so a stray NULL is harmless there. The trap is unique toNOT IN.

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 fromorders 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 keepNOT IN, exclude nulls in the subquery withwhere 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.