Correlated Subquery

What Makes a Subquery "Correlated"?

A correlated subquery references a column from the outer query inside its own WHERE clause (or SELECT list). Because that outer column's value changes for every outer row, the subquery can't be evaluated once and reused — conceptually, the engine re-runs it once per outer row, using that row's value each time. This contrasts with an uncorrelated subquery (like those in 17.1–17.3), which references nothing from the outer query and can be evaluated exactly once, its single result reused for every row.

Sample data used throughout this chapter:

employees

departments

Classic Example: Above Your Own Department's Average

sql
SELECT name, department, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department = e.department   -- references the outer row e
);

Walk through it per department:

• Engineering avg = (95000+72000+68000)/3 = 78333.33 → only Alice (95000) beats it.
• Sales avg = (60000+88000+55000)/3 = 67666.67 → only Eve (88000) beats it.
• Marketing avg = (70000+62000)/2 = 66000.00 → only Grace (70000) beats it.

Result:

Notice the inner AVG(salary) is computed differently for each outer row, because e.department changes each time — that's correlation.

Why "Once Per Outer Row" Matters for Performance

Conceptually (and in a naive execution plan), the database evaluates the inner query separately for each of the 8 outer rows — 8 little aggregate scans instead of 1. On a small table this is invisible; on a table with millions of rows, a naively-executed correlated subquery can be dramatically slower than an equivalent JOIN (see 17.12), because the work scales as outer-rows × inner-query-cost. In practice, modern optimizers (PostgreSQL, SQL Server, Oracle) are often smart enough to rewrite a correlated subquery into a join or semi-join internally, avoiding the literal per-row re-execution — but this isn't guaranteed for every shape of query, so the "naive" cost model is still worth understanding.

Uncorrelated vs Correlated, Side by Side

• Uncorrelated: (SELECT AVG(salary) FROM employees) — no outer reference, one number, reused everywhere.
• Correlated: (SELECT AVG(salary) FROM employees WHERE department = e.department) — outer reference e.department, a potentially different number per row.

Key Takeaways / Q&A

Q: How do you spot a correlated subquery just by reading it? A: Look for a column inside the subquery's WHERE clause that belongs to a table declared in the outer query, not the subquery's own FROM.

Q: Can a correlated subquery appear anywhere besides WHERE? A: Yes — SELECT (17.9) and EXISTS (17.5) are the two other very common homes for correlation.

Q: Is correlation itself a performance problem? A: Not inherently — it's a shape, not a guarantee of slowness. EXISTS-based correlated subqueries are often fast because the engine can stop at the first match. The real risk is a correlated scalar subquery in SELECT over a very large outer table with no optimizer rewrite available.