PatternSQL
Rows in one table but not another
Users who never placed an order, products never sold: the NOT EXISTS anti-join, and why it beats NOT IN once NULLs are in play.
Last updated
Pattern
SELECT u.* FROM users AS u WHERE NOT EXISTS ( SELECT 1 FROM orders AS o WHERE o.user_id = u.id );
Why it’s written this way
NOT EXISTS asks, per row of users, "does any matching row exist in orders?" and keeps the row only when the answer is no. It short-circuits at the first match it finds and never inspects the actual values coming back from orders, which is what keeps it safe from the NULL trap below.
NOT IN (SELECT user_id FROM orders) looks like the same query but isn't: if even one row in orders has a NULL user_id, SQL's three-valued logic makes every NOT IN comparison evaluate to UNKNOWN, and the outer query silently returns zero rows instead of erroring.
Edge cases to know
- →NOT IN is fine when the subquery column is guaranteed NULL-free — the danger is that it fails silently the moment it isn't, which is easy to miss until the result set unexpectedly goes empty.
- →LEFT JOIN orders o ON o.user_id = u.id WHERE o.user_id IS NULL is a third way to write the same anti-join; most query planners turn all three into an equivalent plan, so pick whichever reads clearest to the next person.
- →Both user_id columns should be indexed for this to stay fast on large tables — otherwise the anti-join degrades into a nested scan per row of users.