Is EXISTS always faster than IN with a subquery, or is that rule of thumb outdated?

## Where the rule of thumb came from "Use EXISTS, never IN" is one of the most durable pieces of SQL advice, and it was once defensible. On older engines that did not transform subqueries, `IN (subquery)` and `EXISTS (subquery)` could compile into genuinely different strategies — one evaluating the inner query once and stashing the result, the other re-evaluating per outer row — with big and predictable differences. The optimizers that made that advice true have largely been superseded, but the advice outlived them, and it now gets repeated in interviews as if it were a law. ## Why the two are the same question Both spellings express a semi-join: keep an outer row when a matching inner row exists, once, regardless of how many matches there are. ```sql -- correlated SELECT c.customer_id FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id); -- uncorrelated SELECT c.customer_id FROM customers c WHERE c.customer_id IN (SELECT o.customer_id FROM orders o); ``` A point worth being explicit about: `IN` does **not** multiply rows. If the subquery returns customer 7 forty times, customer 7 still appears exactly once in the result, because `IN` tests membership in a set rather than pairing rows. That is precisely what distinguishes both of these from an inner join, and it is why neither needs a `DISTINCT`. ## What actually differs: the shape you hand the planner Since the semantics match, the interesting question is what each form suggests as a strategy. - The **correlated EXISTS** reads as: for each outer row, probe the child for a match and stop. That is cheap when the outer side is already small or heavily filtered and the child's join key is indexed. Its total cost scales with the number of outer rows that reach the predicate. - The **uncorrelated IN** reads as: produce the set of child keys once, then test each outer row against it. That is cheap when the child set is small relative to the outer table, or when the outer side is large enough that a per-row probe would be repeated far too often. A good optimizer considers both strategies for either spelling, and picks by estimated cost. So the practical guidance is: the form you write is a hint about intent, not a performance decision, and the way to settle a performance question is to read the plan for both. ## When the spelling still matters in practice There are real cases where you should pay attention rather than shrug: 1. **The child key is not indexed.** Then the correlated form risks a repeated scan of the child table, which is a much worse failure mode than building one structure over it. Fix the index before you fix the spelling. 2. **The estimates are wrong.** If the planner badly misjudges how many outer rows survive the other predicates, it may choose a per-row probe for millions of rows, or build a large structure to serve three. The idiom did not cause that; the estimate did — but the idiom is often what gets blamed. 3. **The subquery is expensive to produce.** If the inner side is itself a heavy aggregation or a multi-table join, whether it is evaluated once or per row is a real difference, and it is worth confirming which one the plan chose. 4. **Extra correlation conditions.** A correlated EXISTS can carry additional predicates referencing the outer row (`AND o.order_date >= c.signup_date`), which the flat `IN (SELECT single_column ...)` form cannot express at all. That is an expressiveness reason to pick EXISTS, not a speed reason, and it is often the real one. ## How to answer this in an interview The answer that lands is not "EXISTS" and not "they are identical". It is: *they express the same semi-join, most modern cost-based optimizers treat them alike, and the difference that survives is the shape of the plan each suggests — per-row probe versus build-once-and-probe. Which wins depends on which side is selective and what is indexed, so I would check the plan rather than apply a rule.* Then add the practical default: write whichever reads more clearly for the query at hand — usually the correlated `EXISTS` when the condition involves more than one column or references the outer row, and `IN` when it is a plain single-column membership test against a short list of keys. ## What not to say Avoid asserting engine-specific behaviour you have not verified: which versions of which engines flatten which subquery forms is a moving target, and a confident wrong claim about it is worse than the honest "it depends on the plan". Avoid the claim that `IN` returns duplicates — it does not. And avoid extending the comparison to the negated forms without care, because `NOT IN` and `NOT EXISTS` differ in ways that go well beyond performance.