-- NOT IN with NULL - THE TRAP!

SELECT name, email

FROM customers

WHERE customer_id NOT IN (

SELECT customer_id

FROM orders

);

-- If orders.customer_id contains ANY NULL value:

-- Returns ZERO rows!

-- Reason: NULL comparisons are unknown, not false

-- customer_id NOT IN (1, 2, NULL) is unknown for all values

-- Use NOT EXISTS instead

SELECT c.name, c.email

FROM customers c

WHERE NOT EXISTS (

SELECT 1

FROM orders o

WHERE o.customer_id = c.customer_id

);

-- Handles NULL correctly

-- Returns customers without orders

-- Always works as expected

NOT IN fails with NULL because NULL comparisons return unknown (not false). If subquery returns (1, 2, NULL), then "customer_id NOT IN (1, 2, NULL)" evaluates to unknown for all values, filtering out all rows. NOT EXISTS doesn't have this problem - it checks existence, not value comparison.

Never use NOT IN. Always use NOT EXISTS. This is a critical SQL gotcha that causes silent bugs.

Critical

NOT IN returns zero rows when subquery contains NULL

-- IN with large result set - SLOW

SELECT c.name, c.email

FROM customers c

WHERE c.customer_id IN (

SELECT customer_id

FROM orders

);

-- For 50,000 customers with orders:

-- Builds hash table of 50,000 IDs

-- Query time: 450ms

-- Use EXISTS for large datasets

SELECT c.name, c.email

FROM customers c

WHERE EXISTS (