Customers who never ordered: NOT EXISTS vs NOT IN

-- ======================================================================
-- Q: Find customers who have never placed an order. Why not NOT IN?
--
-- x NOT IN (1, 2, NULL) evaluates to x <> 1 AND x <> 2 AND x <> NULL, and the
-- last one is UNKNOWN, so no row passes. NOT EXISTS does not have this problem
-- and is usually faster too. I ran into this in the ASP migration: the legacy
-- data had orders with an empty customer, and a report using NOT IN suddenly
-- returned an empty result.
-- ======================================================================

-- Correct: anti-join, the planner turns it into a Hash Anti Join
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Dangerous: if even one orders.customer_id is NULL, it returns 0 rows
SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);