Asked by Samuel Okafor · 467 views · 2 replies
Trying to find products that have never been ordered:
SELECT * FROM products
WHERE id NOT IN (SELECT product_id FROM order_items);Zero rows. There are definitely products with no order items — the LEFT JOIN version finds forty of them. Same data, same database.
order_items.product_id contains at least one NULL. That is enough to make the whole thing return nothing.
x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL, and that last comparison is UNKNOWN rather than true — so the row is never returned, for any value of x. One NULL in the subquery empties the entire result.
Use NOT EXISTS, which is null-safe and usually gets a better plan as well:
SELECT p.*
FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);Your LEFT JOIN version works for the same reason — it compares rows rather than evaluating a list containing NULL.
Once it is fixed, the next question is why that column is nullable at all. If an order item must always reference a product, a NOT NULL constraint stops this class of bug at the source instead of in every query that touches it.
There were eleven NULLs, all from an import last year. Switched to NOT EXISTS and I am adding the constraint after I clean them up.