Find users who registered but never purchased — and why NOT IN can return zero rows
Tables users(user_id) and orders(user_id), where orders.user_id is NULL on a few guest-checkout rows. Return every user who registered but never placed an order. Note — a NOT IN (SELECT user_id FROM orders) version returns zero rows on this data, so your query must avoid that trap.
-- return users.user_id for users with no matching order rowWrite the query.
Use an anti-join: SELECT u.user_id FROM users u LEFT JOIN orders o ON o.user_id = u.user_id WHERE o.user_id IS NULL, or NOT EXISTS. NOT IN (SELECT user_id FROM orders) returns zero rows when the subquery holds any NULL, because x NOT IN (…, NULL) is UNKNOWN.
- ✗Trusting NOT IN when the subquery may contain NULL
- ✗Using INNER JOIN, which drops the unmatched users entirely
- ✗Filtering o.user_id IS NULL after an inner join
- →How does NOT EXISTS avoid the NULL problem?
- →Would filtering NULLs out of the subquery fix NOT IN?
Return the users with no matching order via an anti-join. The LEFT JOIN keeps every user and pads the right side with NULL when there is no order; filtering o.user_id IS NULL keeps exactly the non-purchasers.
SELECT u.user_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
WHERE o.user_id IS NULL;NOT EXISTS is equally correct and NULL-safe:
SELECT u.user_id
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);NOT IN (SELECT user_id FROM orders) breaks because a single NULL in the list makes user_id NOT IN (..., NULL) evaluate to UNKNOWN for every row — never TRUE — so the whole result is empty.
