NOT IN, NULL and NOT EXISTS.

The trap: x NOT IN (list) means "x differs from every value". If the list contains even one NULL, comparing x with it is unknown, not true, and NOT IN returns no rows at all. orders.customer_id contains NULL (a guest order without a customer), so id NOT IN (SELECT customer_id FROM orders) can return zero customers even though some have no orders.

The fix is NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id): it asks whether a linked row exists and NULL does not confuse it. Another option is to filter the subquery with WHERE customer_id IS NOT NULL. The preferred way in these tasks is NOT EXISTS.

Common mistakes: NOT IN against a column that may be NULL; testing = NULL instead of IS NULL; expecting NOT IN and NOT EXISTS to always agree — they do not.

Small example:

SELECT c.id, c.name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total >= 100);