JOIN multiplies rows
A join doesn't filter the left table. It pairs each row on the left with each matching row on the right. So A customer with three orders makes three result rows.
When you want the orders, that is just right. When you want the customers, it's a bug, and the usual patch is DISTINCT. That works, but it's worth knowing what it costs: the DB made the duplicate rows and then sorted or hashed them away. EXISTS never makes them in the first place.
The rule of thumb: join when you want columns from the other table, use EXISTS when you only want to know whether it's there.
EXISTS stops early
EXISTS (subquery) is true if the subquery returns at least one row. Not how many, not which — just whether. That is why SELECT 1 is the conventional body: nothing in the select list is ever read. There's no reason to name a column.
Because only existence matters, the engine can stop scanning the moment it finds one match. For a customer with ten thousand orders, EXISTS looks at one.
EXISTS is also *linked*: the subquery refs c.id from the outer query, so it's conceptually re-tested per outer row. Planners rarely execute it that truly, but writing it that way is what makes the early exit available.
Where NOT IN breaks
Run the fourth variant. NOT IN returns no rows at all, and NOT EXISTS returns Alan and Edsger, which is the correct answer.
Nothing is wrong with the data. Order 4 is anonymous, so its customer_id is NULL, and the subquery returns the list (1, 1, 2, NULL).
c.id NOT IN (1, 1, 2, NULL) expands to:
c.id <> 1 AND c.id <> 1 AND c.id <> 2 AND c.id <> NULL
The final check isn't false. It's unknown. Alan's id is 3, and whether 3 differs from an unknown value can't be found. AND with unknown gives unknown, WHERE keeps only rows that are definitely true, and so nothing lives. each row is filtered out, for each customer, silently and without an error.
NOT EXISTS is unaffected because it never checks any of it to NULL. It asks whether a matching row was found. The answer to that is always yes or no.
This is the hands-on rule, and it's worth committing to memory:
> **NOT IN with a nullable subquery column is a bug.** Use NOT EXISTS, or > add WHERE customer_id Isn't NULL to the subquery.
The plus forms don't have this problem. IN returns true as soon as it finds a real match, and an unknown among the left checks can't take that away.
Twenty years ago these three had truly different costs and the advice was elaborate. Modern planners in PostgreSQL, SQL Server and Oracle recognise all three shapes and often compile them to the same physical plan — often a semi-join, which is just "find whether a match exists, don't duplicate rows".
So speed is rarely the reason to choose. The reasons that live are:
| Want | Use | Because |
|---|
| Columns from the other table | JOIN | it is the only one that gives them |
| Just to know it exists | EXISTS | no duplicates, early exit |
| Absence, nullable column | NOT EXISTS | NOT IN returns nothing |
| A short literal list | IN | IN (1, 2, 3) reads better than anything else |
Check the plan when it matters. EXPLAIN settles in ten seconds what a rule of thumb argues about indefinitely.
The NOT IN failure, run in this engine
The NOT IN trap is worth seeing execute rather than thinking about, because it makes no error — only a wrong answer. Three customers, an orders table where one order is anonymous (a NULL customer_id), asked which customers never sorted:
| query | result |
|---|
... WHERE id NOT IN (SELECT customer_id FROM orders) | zero rows |
... WHERE NOT EXISTS (SELECT 1 ...) | customer 3 (correct) |
The single NULL in the subquery poisons the whole NOT IN: 3 <> NULL is unknown, AND with unknown is unknown, and WHERE keeps only rows that are definitely true, so each customer is filtered out. NOT EXISTS never checks any of it to NULL. It asks whether a matching row was found — so it returns the right answer on the same data. This isn't a corner case to file away; a nullable column is the normal situation. This Is why NOT EXISTS is the safe default and NOT IN earns its place only on a column that is provably NOT NULL.
EXISTS stops at the first match
The early exit is the other reason to prefer EXISTS, and it's a real difference in work done. EXISTS is true the moment one matching row is found, so for a customer with ten thousand orders it checks one. COUNT(*) > 0, the common option, counts all ten thousand before checking:
| test | rows the subquery examines |
|---|
EXISTS (SELECT 1 ...) | 1 (stops at the first) |
(SELECT COUNT(*) ...) > 0 | all of them |
The two return the same answer and do wildly different amounts of work, and the gap grows with how many matches there are. Just the case where you least want to count them. So the habit worth forming is EXISTS for "is there at least one", never COUNT(*) > 0: the question is existence, and existence is answered by the first row, not the last.
Questions people ask about EXISTS, IN and JOIN
Is EXISTS faster than IN? Not always. Modern optimisers often turn both into the same plan. EXISTS can stop at the first match, which helps when the subquery is large.
Why does NOT IN return no rows when the subquery has a NULL? Because x NOT IN (1, NULL) evaluates to unknown, never true. Use NOT EXISTS, or filter NULLs out of the subquery.
When should I use a JOIN instead of EXISTS? Use a JOIN when you need columns from the other table. Use EXISTS when you only need to know whether a match exists.
Can a JOIN return duplicate rows where EXISTS does not? Yes. If one parent matches many children, a JOIN returns the parent once per child. EXISTS returns each parent once.
What does SELECT 1 inside EXISTS mean? Nothing special. EXISTS only checks whether any row comes back, so the selected columns are ignored.
How do I find rows with no match? Use NOT EXISTS, or a LEFT JOIN with a WHERE clause that checks the right side is NULL. Both are safe with NULLs.
Where it goes wrong with EXISTS, IN and JOIN
**NOT IN on any of it nullable.** The query returns zero rows and looks like a data problem.
**SELECT * inside EXISTS.** Harmless but misleading — it suggests the columns are read, and they never are.
**DISTINCT as a reflex.** If a join is making duplicates, ask whether you wanted the join at all. DISTINCT hides the symptom and pays for the rows twice.