Unit 06.03: The NOT IN with NULL bug

Unit ID: SQL-M06-U04 - Estimated active time: 14-17 minutes Objective: explain why NOT IN can return zero rows, and write the version that cannot.

The bug, in our data

Customers who have never ordered, using NOT IN:

SELECT COUNT(*)
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
-- 3812

Correct - matches the LEFT JOIN answer from Module 5. Now watch what a single NULL in the subquery does:

SELECT COUNT(*)
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders UNION ALL SELECT NULL);
-- 0

Zero rows. Not fewer rows - none at all. One NULL destroyed the entire result.

Why it collapses

x NOT IN (a, b, NULL) expands to:

x <> a AND x <> b AND x <> NULL

That final comparison is UNKNOWN for every possible x. TRUE AND UNKNOWN is UNKNOWN, so the whole condition is never TRUE, so WHERE keeps nothing. It is Module 3's three-valued logic producing a catastrophic rather than a subtle result.

IN behaves differently and is safe:

SELECT COUNT(*) FROM customers WHERE country IN (SELECT country FROM customers);
-- 4512

TRUE OR UNKNOWN is TRUE, so matches still work. The 300 NULL-country customers are simply not matched. IN degrades gracefully; NOT IN fails completely.

Two safe rewrites

-- Preferred: NOT EXISTS is immune to the problem
SELECT COUNT(*)
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
-- 3812

-- Or exclude NULLs explicitly from the list
SELECT COUNT(*)
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);
-- 3812

NOT EXISTS is the better habit. It is correct regardless of NULLs, and it does not require you to remember the hazard every time.

Why this one is worth memorising

A query returning zero rows is usually assumed to mean "no matching data". Here it means "your subquery contained a NULL". Those are very different conclusions, and the query gives no hint which applies.

Practice

A colleague's query returns 0 rows and they have concluded there are no unpaid orders. Their query uses NOT IN against a subquery selecting from payments. What do you check first?

SELECT COUNT(*) AS rows, COUNT(order_id) AS non_null
FROM payments;
-- 1185 | 1185

Takeaway

NOT IN plus one NULL returns nothing at all. Default to NOT EXISTS and treat any surprising zero-row result as a NULL question before a data question.

---

Mark complete