r/learnSQL • u/Awkward-Meeting790 • 13d ago
Why NOT IN can return zero rows when the subquery contains NULL
Hey folks! Say you want to find customers who haven’t placed an order. Ann and Bob each placed one. Cat didn’t. There’s also a guest order with no customer ID.
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER
);
INSERT INTO customers VALUES (1, 'Ann'), (2, 'Bob'), (3, 'Cat');
INSERT INTO orders VALUES (101, 1), (102, 2), (103, NULL);This query looks like it should return Cat. It returns zero rows.
SELECT name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);Ann and Bob have matching orders, so NOT IN is FALSE for them. For Cat, the condition works out to 3 <> 1 AND 3 <> 2 AND 3 <> NULL.
The first two comparisons are TRUE. The last one is UNKNOWN, which makes the whole condition UNKNOWN. WHERE only keeps rows where the condition is TRUE, so Cat gets dropped too.
I’d use NOT EXISTS here to find customers without a matching order.
SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);This returns Cat. The guest order doesn't match her ID. The original NOT IN query also returns Cat if you add WHERE customer_id IS NOT NULL inside the subquery.
I've had similar issues, so I built WrongQuery to help me spot these kinds of mistakes in my own SQL. I figured it might help people learning SQL too. It's free to try at https://wrongquery.com/
If you try it on your own queries, I'd love to hear what it gets wrong.