SQL Quest › SQL Interview Questions › SQL traps › NOT IN with NULL

SQL NOT IN with NULL: why the query returns 0 rows

If the subquery behind NOT IN returns even one NULL, NOT IN is never true for any row, and the query quietly returns nothing.

The task

Count the chargebacks that were opened on a day when no chargeback was resolved. The chargebacks table has 76 rows; 17 of them are still open, so their resolved_at is NULL. The data is SQL Quest's synthetic card-transactions set (accounts, merchants, transactions, chargebacks), and every result on this page is what the query returns on it.

The query that looks right

SELECT COUNT(*) AS chargebacks
FROM chargebacks
WHERE DATE(opened_at) NOT IN (
  SELECT DATE(resolved_at) FROM chargebacks
);

Returns: 0

0 — not a single chargeback.

Why

The fix

Fix 1 — NOT EXISTS — ignores NULLs by construction

SELECT COUNT(*) AS chargebacks
FROM chargebacks c
WHERE NOT EXISTS (
  SELECT 1 FROM chargebacks r
  WHERE DATE(r.resolved_at) = DATE(c.opened_at)
);

Returns: 49

Fix 2 — Keep NOT IN, but take the NULLs out of the subquery

SELECT COUNT(*) AS chargebacks
FROM chargebacks
WHERE DATE(opened_at) NOT IN (
  SELECT DATE(resolved_at) FROM chargebacks
  WHERE resolved_at IS NOT NULL
);

Returns: 49

Right answer: 49 chargebacks were opened on a day with no resolution.

Rule of thumb. Prefer NOT EXISTS for "rows with no match". If you use NOT IN, make sure the subquery cannot return NULL.

Read next: Five NULL-handling mistakes, including COUNT and =

Where this trap is tested

This trap is question 9 of our Capital One CodeSignal-style mock — a timed practice screen on the same kind of data. The mock asks it in a different form, so this page does not give its answer away.

Practise it

Related SQL traps