Subquery vs Join

Two Tools, Overlapping Jobs

Many questions answerable with a subquery can also be answered with a JOIN, and vice versa. Neither is universally "correct" — the right choice depends on what you're asking, how the optimizer handles it, and how readable the result is to the next person.

Sample data used throughout this chapter:

employees

departments

Case 1: EXISTS vs JOIN + DISTINCT

"Which departments have at least one employee?"

sql
-- EXISTS version
SELECT d.name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department = d.name);

-- JOIN version
SELECT DISTINCT d.name
FROM departments d
JOIN employees e ON e.department = d.name;

Both return the same 3 rows (Engineering, Sales, Marketing). But the JOIN version has a subtle trap: since every department here has multiple matching employees, the join produces one output row per matching employee, and DISTINCT has to clean up the resulting duplicates afterward. EXISTS never produces duplicates in the first place — it's a pure existence test, not a row-multiplying operation. For "does a match exist" questions, EXISTS is usually both clearer and avoids relying on DISTINCT to paper over a row explosion.

Case 2: Correlated Subquery vs Join

"Employees earning more than their own department average" (17.4) can be rewritten as a join against a pre-aggregated derived table (17.10):

sql
-- Correlated subquery version
SELECT name, department, salary
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

-- JOIN version
SELECT e.name, e.department, e.salary
FROM employees e
JOIN (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_avg ON dept_avg.department = e.department
WHERE e.salary > dept_avg.avg_salary;

Both return Alice, Eve, Grace. The join version computes each department's average once (one GROUP BY pass), then joins it back — generally cheaper at scale than a naive per-row re-evaluation of the correlated subquery, though a good optimizer may rewrite the subquery version into essentially the same plan anyway.

General Guidance

• Joins are often (not always) more efficient for combining/filtering row sets, because modern query optimizers have decades of tuning specifically for join algorithms (hash join, merge join, nested loop) and their cost estimation.
• EXISTS/NOT EXISTS are often clearer than an equivalent join+DISTINCT for pure "does a match exist / does no match exist" questions — they say what you mean without needing a cleanup step.
• Correlated scalar subqueries in SELECT (17.9) are a hidden performance trap at scale — they look innocent (a single extra computed column) but can force a per-row re-evaluation that a window function or joined pre-aggregate would avoid.
• Not everything converts cleanly — some correlated logic is often more naturally expressed as a subquery or window function than as a join.

Edge Cases

• A JOIN can silently change row counts (duplicating outer rows) in ways a subquery never does, since subqueries used in WHERE only filter, never multiply, the outer row set.
• NOT EXISTS has no clean JOIN-only equivalent without an IS NULL anti-join trick (LEFT JOIN ... WHERE right.key IS NULL), which is less obviously correct at a glance than NOT EXISTS.

Key Takeaways / Q&A

Q: Is a JOIN always faster than a subquery? A: Often, but not always — it depends on the optimizer, indexes, and data distribution; measure, don't assume.

Q: When is EXISTS clearly better than JOIN+DISTINCT? A: When the question is really "does at least one related row exist" — EXISTS answers that directly without ever creating duplicate rows to clean up.

Q: What's the anti-join trick for NOT EXISTS using only JOIN syntax? A: LEFT JOIN ... ON ... WHERE right_table.key IS NULL — finds left rows with no matching right row, but it's less readable than NOT EXISTS.