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".
SELECT first_name, salary FROM employees AS e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department_id = e.department_id );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
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 BYderived table (third example) or a window function is often faster and reads well. NULLgroups. In the first example, employees with aNULLdepartment_idnever matchx.department_id = e.department_id, so the subquery returnsNULLand 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.