SQL Academy · Lesson

Correlated Subqueries

A subquery that depends on the outer row.

What Is a Correlated Subquery?

A correlated subquery is a subquery that references a column from the outer (enclosing) query. Unlike a regular subquery that runs once and returns a fixed result, a correlated subquery is evaluated once for every row processed by the outer query.

This makes them powerful for row-by-row comparisons, but also more expensive than simple subqueries.

Simple vs Correlated Subquery

The key difference: a regular subquery has no reference to the outer query and can stand alone. A correlated subquery depends on the outer row — you can see the outer table alias appearing inside the subquery.

In the example below, the inner SELECT references e1.department_id from the outer query, creating the correlation.

-- Regular subquery (runs once)
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated subquery (runs once per outer row)
SELECT name, salary, department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

All lessons in this course

← Back to SQL Academy