You want customers who never placed an order. `WHERE customer_id NOT IN (SELECT customer_id FROM orders)` returns zero rows, though you know some exist. Why?

The orders.customer_id column contains at least one NULL. NOT IN compares the value against every value in the list, and a comparison with NULL gives "unknown", not true. If there is no match and at least one NULL in the list, the whole NOT IN is NULL for every row, so the filter keeps nothing. Use NOT EXISTS or an anti-join instead.

Why one NULL breaks everything

42 NOT IN (7, 9, NULL) means 42 <> 7 AND 42 <> 9 AND 42 <> NULL. The first two are true, the last is unknown, and true AND unknown is unknown. WHERE only keeps rows where the condition is true, so customer 42 is dropped, and so is everyone else.

Guest checkouts, deleted accounts or a broken load can leave NULL customer IDs in orders. The query works for months, then one NULL arrives and the report goes blank.

The fixes

-- NOT EXISTS: not affected by NULLs
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- Anti-join with LEFT JOIN
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Both return customers with no matching order, whatever NULLs the orders table holds. In the LEFT JOIN form, test a column that is never NULL in a real match, such as the primary key.

If you must keep NOT IN, filter the NULLs inside: NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL). It works, but it is easy to forget in the next query, so prefer NOT EXISTS as the habit.

Related traps

• WHERE status <> 'cancelled' also drops rows where status is NULL. Write status IS DISTINCT FROM 'cancelled' if NULL should count as "not cancelled".
• IN with NULLs does not break the same way: it is true whenever a match exists, and a NULL in the list only matters for values with no match.

Check the data too

A NULL customer ID on an order is often a data quality problem worth raising. Count them, and ask whether the column should be NOT NULL.

What interviewers want: three-valued logic, why one NULL makes NOT IN return nothing, NOT EXISTS or an anti-join as the fix, and noticing the data issue behind it.