Subquery or Join?
IN, EXISTS and JOIN can answer the same question; choose on row multiplication, NULL semantics and what you need to select.
WHERE id IN (SELECT fk FROM t2) -- vs -- JOIN t2 ON t2.fk = t1.id"Which customers have a paid order?" can be written with IN, with EXISTS, or with an inner join. They are not interchangeable by accident — they differ in exactly one respect that matters, and mostly agree on the rest.
The difference that bites: row multiplication
IN and EXISTS are semijoins. Each outer row is returned at most once, however many inner rows match. A join is not: a customer with four paid orders appears four times, which is why rewrites of this kind so often grow a DISTINCT that hides a real change in meaning. If you only need to test for a match, use a subquery. If you need columns from the other table, use a join and accept the multiplication as correct.
Performance is mostly not the tiebreaker
In MySQL 8.4 a subquery in an IN, = ANY or EXISTS predicate at the top level of WHERE or ON is transformed into a semijoin, executed by one of five strategies: Table Pullout, Duplicate Weedout, FirstMatch, LooseScan, or Materialization. Since MySQL 8.0.17, NOT IN and NOT EXISTS become antijoins. The old advice that "joins are always faster than subqueries" predates this.
It matters when the subquery is not eligible. The transform requires a single SELECT with no UNION, no HAVING, no aggregate functions and no LIMIT. Fail any of those and MySQL falls back to materialization or the EXISTS strategy — that is the case where rewriting as a join or a derived table genuinely pays. Read EXPLAIN; do not guess.
Example
-- Same question, three spellings.
-- 1. Semijoin: one row per customer, no matter how many paid orders they have.
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders AS o
WHERE o.customer_id = c.id AND o.status = 'paid');
-- 2. Same result, membership phrasing. IN is an alias for = ANY.
SELECT c.id, c.name
FROM customers AS c
WHERE c.id IN (SELECT o.customer_id FROM orders AS o WHERE o.status = 'paid');
-- 3. NOT the same result: one row per matching ORDER, not per customer.
-- Correct only if you actually want the order columns.
SELECT c.id, c.name, o.id AS order_id, o.total
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id AND o.status = 'paid';When to use it
- Decide between EXISTS and a join when a report is showing duplicate parent rows.
- Replace a NOT IN that broke on a nullable column with NOT EXISTS or a LEFT JOIN anti-join.
- Rewrite a subquery that EXPLAIN shows is being materialised rather than turned into a semijoin.
- Aggregate a child table in a derived table so the totals join back one row per parent.
More examples
Choosing by what the select list needs
The decision is driven by the shape of the output, not by a preference for one syntax. Ask two questions in order: do I need columns from the other table, and do I want one row per outer row? Existence only means EXISTS; other columns at natural multiplicity means a join; one row per parent but carrying a value from the child means aggregate first in a derived table or CTE.
-- Need: 'which customers ordered at all in 2026' -> existence only -> semijoin.
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders AS o
WHERE o.customer_id = c.id AND o.ordered_at >= '2026-01-01');
-- Need: 'what did each of them order' -> columns from orders -> join.
SELECT c.name, o.id, o.ordered_at, o.total
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.ordered_at >= '2026-01-01';
-- Need: 'how much did each of them spend' -> one row per customer, but a value
-- from orders -> aggregate in a derived table, then join.
SELECT c.name, spend.total_2026
FROM customers AS c
JOIN (SELECT customer_id, SUM(total) AS total_2026
FROM orders WHERE ordered_at >= '2026-01-01'
GROUP BY customer_id) AS spend
ON spend.customer_id = c.id;Three ways to write an anti-join, and why one is wrong
Use this as a checklist when you inherit an anti-join. The first two are equivalent and both correct; pick whichever reads better in context. The third is only safe when the subquery column is declared NOT NULL, and the MySQL 8.4 manual also notes that the antijoin transformation usually cannot be applied when the compared expressions are nullable, so the unsafe form tends to be the slow one too.
-- Safe. Unaffected by NULLs in orders.customer_id.
SELECT c.id FROM customers AS c
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);
-- Safe. The classic outer-join spelling; test a column that is never NULL
-- in a matched row.
SELECT c.id FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;
-- Unsafe. NOT IN is <> ALL, so a single NULL customer_id returns zero rows.
SELECT c.id FROM customers AS c
WHERE c.id NOT IN (SELECT o.customer_id FROM orders AS o);When the rewrite actually matters
This is the case where the choice is not cosmetic. Run EXPLAIN on both: the first plan will show the subquery being materialised into a temporary table, while the second is a plain join whose order the optimizer is free to choose. The rewrite is safe here without DISTINCT because GROUP BY product_id already guarantees at most one row per product, and it hands you the units column for free.
-- Not eligible for semijoin: the subquery has GROUP BY with an aggregate and
-- a HAVING clause, so MySQL falls back to materialization or the EXISTS strategy.
SELECT p.id, p.name
FROM products AS p
WHERE p.id IN (SELECT oi.product_id
FROM order_items AS oi
GROUP BY oi.product_id
HAVING SUM(oi.qty) > 100);
-- Rewritten as a join against a derived table, giving the optimizer an ordinary
-- join to cost and letting you select the aggregate as well.
SELECT p.id, p.name, hot.units
FROM products AS p
JOIN (SELECT oi.product_id, SUM(oi.qty) AS units
FROM order_items AS oi
GROUP BY oi.product_id
HAVING SUM(oi.qty) > 100) AS hot
ON hot.product_id = p.id;
Discussion