Correlated Subquery
SQL · SQL Subqueries
Theory
A correlated subquery refers to a column of the outer query, so its result depends on the current outer row. In WHERE salary > (SELECT AVG(salary) FROM employees i WHERE i.dept = e.dept), the inner AVG is computed for the department of each employee e. Logically it re-runs once per outer row: Asha is compared with the IT average, Ravi with the HR average, and so on.
This makes it a natural fit for "compare each row with its own group" questions, and for EXISTS / NOT EXISTS checks. Aliases are essential to show which table each column belongs to; a missing alias can silently correlate to the wrong table. Run naively, it costs one inner query per outer row, although modern optimisers often rewrite it into a join. When performance matters, a JOIN to a grouped subquery or a window function like AVG(salary) OVER (PARTITION BY dept) is usually faster.
Why we use it
Some comparisons are relative to each row's own context: above the department average, latest order per customer, price higher than the category average. A correlated subquery expresses this directly.
When to use it
Use correlated subqueries for per-row comparisons and EXISTS checks, especially on small or well-indexed data. For large tables, prefer window functions or a join to a pre-aggregated table.
How it works
- Take an outer row (employee e)
- Pass e.dept into the inner query
- Inner query computes the average for that dept
- Is e.salary greater than that average? Yes: Keep the outer row. No: Discard it.
- Move to the next outer row and repeat
- Return kept rows
Syntax
SELECT columns FROM t1
WHERE column = (SELECT ... FROM t2 WHERE t2.id = t1.id);Example
SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);Key points
- References a column from the outer query
- Logically re-runs once per outer row, so it can be slow
- Common for row-by-row comparisons against a group
- Always alias tables to make the correlation clear
- Window functions or joins are often faster alternatives