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
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.
Part by part
- WHERE NOT EXISTS (
- Returns rows for which the subquery finds nothing. It stops at the first match rather than counting, which is why it performs well.
- SELECT 1
- The column list is irrelevant — EXISTS tests for the presence of a row, not its contents. Writing 1 makes that explicit; SELECT * would be equivalent and misleading.
- WHERE o.user_id = u.id
- The correlation to the outer query. This reference is what makes the subquery run per user rather than once for the whole table.
Common mistakes
WHERE u.id NOT IN (SELECT user_id FROM orders)If any user_id in orders is NULL, the comparison is unknown for every row and the query returns nothing at all. It is the classic NOT IN trap and it fails silently rather than erroring.
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id)LEFT JOIN orders AS o ON o.user_id = u.id WHERE o.id IS NULLCorrect, and it materialises the join before discarding almost all of it. NOT EXISTS can stop at the first match per row, which is usually the cheaper plan on a large child table.
WHERE (SELECT COUNT(*) FROM orders AS o WHERE o.user_id = u.id) = 0Counts every matching row to establish that there are none. EXISTS answers the same question and stops at the first one, which on a user with thousands of orders is a large difference.
Questions
Why is NOT IN dangerous with NULLs?
Because SQL's three-valued logic makes any comparison with NULL unknown, and NOT IN is a chain of such comparisons. A single NULL in the subquery makes the whole condition unknown for every row, so nothing is returned.
Does it matter what I select in the subquery?
No. EXISTS only checks whether a row came back, so the select list is never evaluated. SELECT 1 is a convention that signals this to the reader rather than an optimisation.
Is NOT EXISTS always the right choice?
It is the safest default, since it handles NULLs correctly and reads clearly. Where you need columns from the other table as well, the LEFT JOIN form is the one that gives them to you.
How do I find rows missing from either side?
A FULL OUTER JOIN with a WHERE testing both keys for NULL returns unmatched rows from both tables at once. MySQL lacks it, so there the answer is two anti-joins combined with UNION ALL.