Correlated Subqueries

Every subquery seen so far has been independent — it could run all on its own, with no knowledge of the outer query. A correlated subquery is different: it references a column that comes from the outer query, which means it cannot be executed in isolation at all. Its result depends on whatever outer row is currently being evaluated, which is what "correlated" refers to.

How Correlated Subqueries Conceptually Execute

Because the inner query depends on a value from the current outer row, you can think of it as re-running once per outer row, each time substituting that row's value in. A real database engine may optimize this internally, but conceptually, a correlated subquery answers a slightly different question for every single row of the outer table.

SQL

-- Employees who earn more than the average salary in THEIR OWN department
SELECT e.employee_name, e.department, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department   -- references the OUTER row
);

Read this carefully: the inner query is not computing one company-wide average like the scalar subquery examples on earlier pages — the WHERE e2.department = e.department line ties the inner AVG to whichever department the current outer row (e) belongs to. So an employee in Engineering is compared against the Engineering average, and an employee in Sales is compared against the Sales average, all within a single statement.

Warning

Because a correlated subquery conceptually re-executes for every outer row, it can be a genuine performance concern on large tables — a naive execution is O(outer rows × inner query cost). Modern query optimizers (PostgreSQL, MySQL 8+, SQL Server) can often rewrite a correlated subquery into an equivalent JOIN or semi-join internally, which recovers much of the performance. Still, always check the actual execution plan (EXPLAIN) on large tables rather than assuming the optimizer rewrote it — an equivalent JOIN is sometimes noticeably faster in practice.

The Equivalent Rewritten as a JOIN

SQL

-- Same result, expressed with a JOIN against a pre-aggregated derived table
SELECT e.employee_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;

Approach	Readability	Typical Performance
Correlated subquery	Very close to the plain-English question	Can be slow on large tables if not optimized well
JOIN with derived table	Slightly more setup, but scales predictably	Aggregation happens once, then a single join

Correlated Subqueries in SELECT

Correlated subqueries are not limited to WHERE — they are just as common in the SELECT list, computing a per-row value that depends on something about that specific row.

SQL

-- For each customer, show how many orders they have placed
SELECT
  c.customer_id,
  c.name,
  (
    SELECT COUNT(*)
    FROM orders o
    WHERE o.customer_id = c.customer_id
  ) AS order_count
FROM customers c;

Note

A correlated subquery is also the mechanism behind EXISTS and NOT EXISTS — those are almost always written as correlated subqueries, since checking "does a matching row exist for this outer row" inherently requires referencing the outer row. The EXISTS page covers that pattern in detail.

A correlated subquery references a column from the outer query, so it cannot run independently.
Conceptually, it re-evaluates once per outer row — a real performance consideration on large tables.
Query optimizers can sometimes rewrite correlated subqueries into JOINs automatically, but not always.
When performance matters, compare EXPLAIN output for the correlated subquery version against an equivalent JOIN.