EXISTS vs IN vs JOIN

## The shape of the problem A huge family of everyday queries has the form "rows from A that have at least one related row in B": customers with orders, articles with comments, users with a failed login today. In relational terms that is a **semi-join** — every output column comes from A, and B is consulted only as a test. SQL offers three common spellings, and they differ far more in the work they generate than in the text you type. ## Spelling 1: EXISTS ```sql SELECT c.customer_id, c.name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id); ``` `EXISTS` is a predicate over a subquery: it is TRUE when the subquery produces at least one row. Two consequences follow. First, the subquery's select list is never evaluated for its values — `SELECT 1`, `SELECT *` and `SELECT o.total` mean exactly the same thing, and no column value flows to the outer query. Second, one qualifying row settles the question, so the engine has no reason to look for the second, third or thousandth match. With an index on `orders(customer_id)` that is a single index probe per candidate customer. The output is at most one row per customer, so nothing downstream has to remove duplicates. ## Spelling 2: IN with a subquery ```sql SELECT c.customer_id, c.name FROM customers c WHERE c.customer_id IN (SELECT o.customer_id FROM orders o); ``` `IN` is a membership test. Duplicates in the subquery result change nothing: a customer with 500 orders passes the test once and appears once. Semantically this is the same semi-join as the EXISTS form, and cost-based optimizers commonly compile both into the same physical semi-join — which is why "EXISTS is always faster than IN" is folklore rather than a rule you should repeat without reading a plan. What the spellings genuinely differ in is the *shape* they hand the planner. The correlated EXISTS suggests a per-outer-row probe into `orders`; the uncorrelated IN suggests building one structure over the distinct order keys and probing it with customers. Which is cheaper depends on which side is small, how selective the other filters are, and which key is indexed. ## Spelling 3: JOIN plus DISTINCT ```sql SELECT DISTINCT c.customer_id, c.name FROM customers c JOIN orders o ON o.customer_id = c.customer_id; ``` A join is not a membership test — it pairs rows. If the average customer has k orders, the join emits roughly N x k rows, and the only reason the query still "works" is the `DISTINCT` collapsing them back down. That `DISTINCT` is a blocking operator: it must sort or hash the entire intermediate result at the full width of the select list before it can emit anything, and it can spill to temporary storage when the result does not fit its memory budget. You paid to build rows you then paid again to destroy. Widen the select list with a few `TEXT` columns and the dedup gets proportionally worse. It is also a blunt instrument: `DISTINCT` compares the whole select list, so it silently collapses parent rows that are legitimately identical, not just the copies the join manufactured. ## The cost profile side by side - **EXISTS**: work is proportional to the number of outer rows tested, times the cost of one probe. Output rows: at most one per outer row. No dedup. - **IN (subquery)**: same result; typically one pass over the inner keys plus a probe per outer row, or the same per-row probe if the planner correlates it. No dedup. - **JOIN + DISTINCT**: work is proportional to the number of matching *pairs*, plus a full sort or hash of that intermediate at projected row width. When k is 1, all three are close. When k is 500, the join does two orders of magnitude more work for an identical answer. ## When the join is still the right idiom Reach for the join when you need something from the child table — an order date, an amount, a count. A semi-join deliberately gives you nothing from B, so if the report shows order columns, EXISTS is the wrong tool. The join is also the natural spelling when the join key is unique on the child side (a one-to-one relationship, or a foreign key pointing at a unique key): there is no fan-out, so there is nothing to deduplicate and no `DISTINCT` belongs in the query at all. And if you need aggregates from B, aggregate in a derived table first and join to that, so the fan-out never reaches the outer select list. ## What to check before you commit Make sure the child side's join key is indexed — every one of these idioms depends on it. Then read the plan for the query you actually wrote: confirm the engine chose a semi-join and that no sort or hash-aggregate appeared to clean up after you. If a `DISTINCT` shows up in a plan for a query whose output should already be unique, that is your signal that a join is doing a semi-join's job.