Correlated Subqueries

A subquery that stands on its own is useful, but many questions need the inner and outer queries to talk to each other. 'Who earns more than their own department's average?' is impossible with an independent subquery because the average you need changes for every employee.

A correlated subquery references a column from the outer query — usually in its WHERE clause — so its result depends on which outer row is currently being examined. The database runs it once for every row the outer query produces, feeding in the outer row's values each time.

That repeated execution sounds expensive, and it can be, but for small-to-medium datasets modern query optimisers handle it well. More importantly, it lets you express per-row logic that no other SQL construct can touch cleanly.

flowchart TD
  O1["Outer row: Ada, dept 1"] --> S1["Subquery: AVG(salary) WHERE dept = 1"]
  O2["Outer row: Eli, dept 2"] --> S2["Subquery: AVG(salary) WHERE dept = 2"]
  S1 -->|"825000"| C1{"Ada 720000 > 825000?"}
  S2 -->|"575000"| C2{"Eli 540000 > 575000?"}
  C1 -->|no| D1["Dropped"]
  C2 -->|no| D2["Dropped"]
  style S1 fill:#0e7490,color:#fff
  style S2 fill:#0e7490,color:#fff

A correlated subquery runs once per outer row, using that row's values to shape its own filter.

How correlation works

Correlation is created simply by using an outer-table alias inside the subquery. In the query below, e.department_id is the outer column and the subquery's WHERE department_id = e.department_id ties the two queries together.

SELECT name, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

Read it as: 'for each employee, compute the average salary of everyone who shares that employee's department, then keep the row only if the employee's own salary exceeds that average.' The subquery cannot run on its own — remove e.department_id and it becomes an independent query with a different meaning entirely.

Employees who earn more than their own department's average salary.

SELECT name, salary, department_id
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

Correlated EXISTS

Correlation pairs naturally with EXISTS. A correlated EXISTS subquery asks 'is there at least one row in this other table that relates to the current outer row?' Because the subquery is re-run for each outer row, the answer can change from row to row.

The example below finds departments whose average salary is above the company average. It uses a correlated scalar subquery in the WHERE clause: for each department d, it computes the average salary of employees in that exact department and compares it to the overall average. Without correlation, you would only be able to compare one fixed average.

Departments whose average salary beats the company average, using a correlated subquery.

SELECT d.name
FROM departments d
WHERE (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = d.id
) > (SELECT AVG(salary) FROM employees);

flowchart LR
  subgraph IND["Independent subquery"]
    I1["Run once"] --> I2["Result reused for all rows"]
  end
  subgraph COR["Correlated subquery"]
    C1["Run for row 1"] --> C2["Run for row 2"]
    C2 --> C3["Run for row 3..."]
  end
  style IND fill:#1e293b,color:#fff
  style COR fill:#0e7490,color:#fff

An independent subquery runs once total; a correlated subquery runs once per outer row.

The classic mistake: forgetting to correlate

The most common error is writing a subquery that looks correlated but is not. If you forget the WHERE department_id = e.department_id clause, the subquery becomes independent and returns the same value for every row.

For example, WHERE salary > (SELECT AVG(salary) FROM employees) finds everyone above the company average, not their own department's average. The query runs without error, but the logic is wrong — a silent failure that only shows up when you inspect the results carefully. Always check that the outer alias appears somewhere inside the subquery.

Compare: independent subquery gives the company average for everyone; correlated gives the department average per employee.

-- Independent: company average
SELECT name, salary,
  (SELECT AVG(salary) FROM employees) AS avg
FROM employees;

flowchart LR
  O["Outer row: Ada"] --> S["(SELECT AVG(salary) FROM employees)"]
  O2["Outer row: Eli"] --> S2["(SELECT AVG(salary) FROM employees)"]
  S -->|"same value"| C["compared against company avg"]
  S2 -->|"same value"| C
  style S fill:#b45309,color:#fff
  style S2 fill:#b45309,color:#fff

Without the correlation, the subquery returns the same company average for every row.

Exercise

List the name, salary, and department_id of every employee who earns more than their own department's average. Use a correlated scalar subquery.

SELECT name, salary, department_id
FROM employees e
WHERE salary > -- add correlated subquery here
;

SELECT name, salary, department_id FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

Exercise

Return the name of every department whose average salary is strictly greater than the company-wide average. Use a correlated subquery to compute each department's average.

SELECT d.name
FROM departments d
WHERE -- add correlated subquery > company average
;

SELECT d.name FROM departments d WHERE (SELECT AVG(salary) FROM employees WHERE department_id = d.id) > (SELECT AVG(salary) FROM employees);

Exercise

This query is trying to find employees who earn more than their department's average, but it actually compares everyone against the company average because the subquery is not correlated. Fix it.

SELECT name
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees)
;

SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

Exercise

Find the highest-paid employee in each department. Return the employee's name, their salary, and the department name. You will need to join to departments and use a correlated subquery to test against the departmental maximum.

SELECT e.name, e.salary, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary = -- correlated subquery for MAX
;

SELECT e.name, e.salary, d.name AS department FROM employees e JOIN departments d ON e.department_id = d.id WHERE e.salary = (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id);

Exercise

How many times is a correlated subquery typically evaluated?

The cost: once per row, every row

Correlation buys expressiveness with work. Because the inner query re-runs for every outer row, a correlated subquery over 10,000 employees runs 10,000 times — the shape people mean when they say a query "doesn't scale". On our seed data that is invisible; on a real table it is the difference between milliseconds and minutes.

Most query planners optimise the common cases well, so this is not a reason to avoid correlation. It is a reason to recognise when a set-based rewrite says the same thing more cheaply: a JOIN against a grouped subquery computes each department's average once and reuses it, rather than recomputing it per employee. When a correlated query feels slow, that rewrite is the first thing to reach for.

Recap

A correlated subquery references an outer-query column, usually via a table alias.
It runs once per outer row, so its result changes depending on the row being examined.
Correlated scalar subqueries let you compare each row against a group-specific aggregate like a department average.
Correlated EXISTS subqueries let you test per-row relationships between tables.
Forgetting to include the outer alias inside the subquery turns it into an independent subquery with a completely different meaning — always double-check the correlation.

Next you will meet Common Table Expressions, which let you pull complex subqueries out into named, reusable building blocks.

How correlation works

Correlated EXISTS

The classic mistake: forgetting to correlate

The cost: once per row, every row

Recap

Checkpoint quiz

Go deeper — technical resources