Correlated Subqueries Explained Simply

The one-sentence difference

If you've read CTEs vs Subqueries, you've already seen a regular subquery — one that's completely self-contained. A correlated subquery breaks that independence on purpose: it reaches outside itself and uses a value from the row currently being processed by the outer query, which means its result is different for every row.

Regular vs correlated, side by side

Here's our employees table:

A regular subquery can run entirely on its own — try it, you don't need anything from outside it:

SELECT AVG(salary) FROM employees;  -- 79166.67, one company-wide number

A correlated subquery can't run alone — it references e1.department, which only exists because of the outer query:

SELECT e1.name, e1.department, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e1.department  -- ← the correlation
);

That single line — e2.department = e1.department — is what makes this correlated. Without it, the inner query would just be a plain company-wide average again.

How it actually executes

Conceptually, the database processes it like this: for each row in the outer query, plug that row's department into the inner query, run the inner query, and compare. Six employees means the inner average gets computed up to six times — once per department value encountered:

A different inner result depending on which outer row is being evaluated — that's the "correlation."

A full worked example

Running the full correlated query from above gives us everyone earning more than their own department's average — not the company-wide average:

Ana beats the Sales average (63,666.67) despite earning less than everyone in Engineering — because the comparison is per department, exactly what the correlation was for. Notice Sam (95,000) also makes the cut, just barely clearing the Engineering average of 94,666.67 — while Zoe (88,000), who earns more than Ana, doesn't qualify at all, since she's being measured against her own department's higher bar.

EXISTS — the other common pattern

The most common real-world use of correlation isn't comparing values — it's checking for existence. "Which customers have placed at least one order over $100?" doesn't need any value from the subquery, just a yes/no per customer:

SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id  -- ← the correlation
    AND o.amount > 100
);

Why correlated subqueries can be slow

In the most naive execution model, a correlated subquery genuinely runs once per outer row — on a million-row table, that's a million subquery executions. In practice, most modern query optimizers are smarter than that: they can often rewrite a correlated subquery internally into a join or semi-join before running it, avoiding the row-by-row cost. But this isn't guaranteed for every query, every database, or every version — so it's worth knowing how to check and how to rewrite one yourself.

Rewriting it as a JOIN or window function

The EXISTS pattern above can be rewritten as a join with DISTINCT, though EXISTS is usually still the clearer version for a pure existence check. More interestingly, the "compare to your group's average" pattern — the classic correlated subquery — has a clean window function equivalent, computed in a single pass instead of once per row:

WITH salaries AS (
  SELECT name, department, salary,
         AVG(salary) OVER (PARTITION BY department) AS dept_avg
  FROM employees
)
SELECT name, department, salary
FROM salaries
WHERE salary > dept_avg;

Notice the WITH here does double duty: it's needed because, just like the correlated subquery's result couldn't be filtered inline, a window function's result can't be filtered in WHERE either — both need a wrapping step. (Full reasoning in Window Functions Explained Like You're 10.) This version computes every department's average exactly once, then compares every row to it — instead of recomputing the average for every single employee.

Common mistakes

• Forgetting the correlation condition. Drop e2.department = e1.department and you've silently turned a per-group comparison into a company-wide one — same syntax shape, completely different answer.
• Assuming EXISTS needs specific columns. It doesn't — SELECT 1, SELECT *, and SELECT id all behave identically inside EXISTS.
• Defaulting to a correlated subquery out of habit. If the comparison is "this row vs. an aggregate of its own group," a window function is usually clearer and faster than a correlated subquery.
• Not checking the execution plan. Don't assume a correlated subquery is slow (or that your database optimized it away) — look at the actual plan if performance matters.