EXISTS vs JOIN for filtering

If you only need to filter table A by whether a related row exists in table B — and you do not need any column from B — use EXISTS, not JOIN. The reason isn’t stylistic; the two operators ask the database to do fundamentally different work.

The shape of the bug: row multiplication

An INNER JOIN physically pairs every row in A with every matching row in B. If one A-row matches 50 B-rows, the join produces 50 rows for that A-row. To filter A you then have to collapse them back with DISTINCT or GROUP BY, which costs another sort or hash pass over an already-inflated intermediate.

EXISTS does a semi-join: for each A-row, it scans B just long enough to find one matching row, then stops and moves on. No multiplication, no duplicates, no DISTINCT.

JOIN + DISTINCT (footgun)

SELECT DISTINCT u.*
  FROM users u
  JOIN orders o ON o.user_id = u.id
 WHERE o.status = 'paid';
-- Builds an inflated join, then dedupes.
-- On a 1-to-many relationship this multiplies rows
-- by the average orders-per-user before deduping.

EXISTS (semi-join)

SELECT u.*
  FROM users u
 WHERE EXISTS (
         SELECT 1
           FROM orders o
          WHERE o.user_id = u.id
            AND o.status = 'paid'
       );
-- Short-circuits on the first match per user.
-- No row multiplication, no DISTINCT needed.

Why “the optimizer will fix it” isn’t a plan

MySQL’s semi-join transformation is documented for IN (SELECT …) and EXISTS (SELECT …) subqueries — the optimizer recognises them as “is there at least one match?” and picks among strategies like FirstMatch, LooseScan, Materialization, or DuplicateWeedout. An explicit JOIN … GROUP BY or JOIN … DISTINCT does not get rewritten back into a semi-join in the general case. Whatever the planner does with row multiplication, it does with row multiplication.

That means writing EXISTS isn’t just a stylistic preference — it’s how you actually request the semi-join machinery. Writing JOIN + DISTINCT and hoping for the rewrite is gambling that you’ll trip whichever specific rewrite rule fits your query shape.

Axis	JOIN + DISTINCT	EXISTS
Intermediate rows	Multiplied by avg matches per A-row	Exactly one per A-row
Dedup cost	DISTINCT / GROUP BY sort or hash	None — never duplicated
Short-circuit	No — full match set built	Yes — stops at first match
Optimizer dependence	Relies on rewrite to semi-join	Already a semi-join — no rewrite needed
Reader intent	Ambiguous — output or filter?	Explicit — filter only

The pattern

Pattern

EXISTS for presence checks

Problem

A team uses INNER JOIN to filter table A by table B, then adds DISTINCT to collapse duplicates. Performance is fine on small data and collapses on large data because the intermediate join is multiplied by the 1-to-many fan-out.

Solution

Replace the join with WHERE EXISTS (SELECT 1 FROM B WHERE B.fk = A.id AND …). The query becomes a semi-join: one row per matching A, B is scanned only until the first match per outer row, and DISTINCT is removed.

Consequences

The plan is stable regardless of optimizer cleverness — no reliance on JOIN → semi-join rewrite. Intent is also clearer to readers: EXISTS says “filter”, JOIN says “combine”.

When the optimizer can’t help: predicate pushdown vs row multiplication

Predicate pushdown — the optimizer’s trick of running WHERE filters before the join to shrink the inputs — is real and useful (see 0005 execution order · MySQL ). It does not, however, prevent row multiplication on a 1-to-many join. Pushdown shrinks the inputs; it doesn’t change the fan-out of the join itself.

So even with a perfect optimizer:

JOIN: build the (possibly-pushed-down) combined dataset, multiplied by the fan-out, hand it to the rest of the query.
EXISTS: per outer row, scan inner just long enough to prove the predicate, drop the inner row, decide whether to keep the outer.

A useful refactor: split filtering and projection

For large datasets the cleanest pattern is to use EXISTS to compute the set of IDs you want, then do a second query (or CTE) to fetch the projection. This isolates the cardinality of the filter step from the width of the projection step:

filter then project

WITH paying AS (
  SELECT u.id
    FROM users u
   WHERE EXISTS (
           SELECT 1 FROM orders o
            WHERE o.user_id = u.id
              AND o.status = 'paid'
         )
)
SELECT u.*, p.profile_json
  FROM users u
  JOIN paying        ON paying.id = u.id
  JOIN user_profiles p ON p.user_id = u.id;

The JOINs now exist solely to bring in columns — there’s no row-multiplication risk because each join key resolves to exactly one row by design.

What to remember

JOIN is for combining; EXISTS is for checking. If your SELECT doesn’t reference any column of the joined table, that’s the signal — switch to EXISTS. Confirm the plan with 0007 EXPLAIN · MySQL : a healthy semi-join shows the chosen strategy (e.g. FirstMatch, Materialization) when you ask for the tree-format plan via EXPLAIN FORMAT=TREE or EXPLAIN ANALYZE. A JOIN + DISTINCT that didn’t get rewritten shows up as a larger rows estimate and Using temporary; Using filesort for the dedup pass.