Skip to content
Quantoros

What's the NULL trap with NOT IN?

SQLLevel: IntermediateSubqueries & CTEs

What's the NULL trap with NOT IN?

Hints
  • Distinguish a missing value from zero, an empty string and false.
  • Test comparison, aggregation and filtering on a tiny missing-value example.

Answer

If the subquery used with NOT IN contains even one NULL, the entire NOT IN returns no results. This is because value NOT IN (NULL, ...) evaluates using three-valued logic and can never be proven TRUE.

-- If any customer_id in orders is NULL, this returns NOTHING
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Safe alternatives:
-- 1. Use NOT EXISTS
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- 2. Filter out NULLs from subquery
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

This bug has wasted a lot of hours in production. NOT EXISTS is almost always safer than NOT IN with subqueries.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions