Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Joins and Subqueries

SQL correlated subqueries

Understand SQL correlated subqueries: subqueries that reference the outer row, how they evaluate, common patterns, performance and rewrites with joins.

A correlated subquery is a subquery that uses a column from the outer query. Because it depends on the current outer row, it cannot be computed once and reused; conceptually it is evaluated again for every row of the outer query.

An ordinary (uncorrelated) subquery is like a constant: "the average salary of the whole company". A correlated one is like a function of the row: "the average salary of this employee's department".

SQL
SELECT first_name, salary
FROM employees AS e
WHERE salary > (
  SELECT AVG(salary) FROM employees WHERE department_id = e.department_id
);

Try it

SQL Employees paid above their department's average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL A correlated subquery in the select list ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL The same question as a join to a derived table ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

How it is evaluated

Think of a loop. For each row of the outer query, the database substitutes that row's values into the subquery, runs it, and uses the result in the outer condition or select list.

The inner query needs its own alias for the table when it reads the same table as the outer query (here x and e). Within the subquery an unqualified column name refers to the nearest table that has it, which is a classic way to write a condition that is always true by accident (WHERE department_id = department_id). Qualify every column in a correlated subquery.

Common patterns

  • Compare with a group aggregate: above-average in the department, latest order per customer, most expensive book per author.
  • Existence checks: EXISTS and NOT EXISTS are almost always correlated.
  • Per-row lookups in the select list: counts or maxima per parent row.
  • UPDATE ... SET col = (SELECT ... WHERE inner.key = outer.key): copy a value from a related table.

Dialect differences

All five major databases support correlated subqueries in WHERE, SELECT and UPDATE. They differ in what is allowed in a FROM-clause subquery that references an outer table: that needs LATERAL (PostgreSQL, MySQL 8.0.14+, Oracle 12c+) or CROSS APPLY (SQL Server, Oracle). SQLite does not allow it. See LATERAL joins.

Usage notes

  • Performance. A naive plan runs the subquery once per outer row. With an index on the correlated column it is a cheap lookup; without one it can be quadratic. Optimizers often "decorrelate" simple cases automatically.
  • Rewrite candidates. If the subquery computes an aggregate per group, a join to a GROUP BY derived table (third example) or a window function is often faster and reads well.
  • NULL groups. In the first example, employees with a NULL department_id never match x.department_id = e.department_id, so the subquery returns NULL and they are excluded.
  • Equal results are not guaranteed to be equal speed. Check the plan with EXPLAIN.
  • Readability. Use clear aliases for the two roles and keep the subquery short; move complicated logic into a CTE.