Introduction
A correlated subquery is a subquery that references one or more columns from the outer (containing) query. Because its result depends on the current row being processed by the outer query, the database conceptually re-executes the correlated subquery once per outer row -- unlike a plain, non-correlated subquery, which is evaluated a single time. Correlated subqueries are commonly used with EXISTS/NOT EXISTS checks, per-row comparisons, and row-by-row aggregation lookups.
Cricket analogy: A correlated subquery is like checking, for every single player on the team sheet, whether their individual score beats their own team's average, recalculated fresh for each player, unlike a plain subquery that computes one fixed number just once for the whole match.
Syntax
SELECT outer_alias.column1
FROM table1 AS outer_alias
WHERE EXISTS (
SELECT 1
FROM table2 AS inner_alias
WHERE inner_alias.foreign_key = outer_alias.primary_key
AND inner_alias.some_condition = 'value'
);Explanation
The key marker of a correlated subquery is that the inner query's WHERE clause references a column from the outer query's table (outer_alias.primary_key above). Because the inner query cannot be resolved independently of the outer row, logically it must run once per outer row -- an important performance implication: on large tables this can be much slower than an equivalent JOIN or a non-correlated subquery unless the referenced columns are properly indexed, since modern optimizers can sometimes rewrite correlated subqueries into semi-joins or anti-joins internally, but this is not guaranteed across all database engines.
Cricket analogy: Because the inner check references the outer batsman's team directly, the scorer must recompute the team average fresh for every single batsman rather than once; on a decade of match records this is far slower than a single JOIN unless the team_id column is indexed.
Example
-- Find employees who earn more than the average salary in their own department
SELECT e1.emp_id, e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);
-- Find customers who have placed at least one order over $1000
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.total_amount > 1000
);Output
The first query returns each employee whose salary beats their own department's average -- the inner AVG is recomputed with a different dept_id filter for every candidate row in employees e1. The second query returns one row per customer that has at least one qualifying order; EXISTS stops scanning as soon as it finds one matching row per customer, which is typically more efficient than a correlated subquery that must compute an aggregate.
Cricket analogy: Finding every batsman whose score beats their own team's average recalculates that team's average fresh for each candidate batsman, just like the recomputed dept AVG per employee; a second query flags every team with at least one century-scorer, stopping as soon as it finds one, faster than computing a full average.
Key Takeaways
- A correlated subquery references a column from the outer query, creating a dependency between inner and outer rows.
- Conceptually it is re-evaluated once per outer row, which can hurt performance on large tables without proper indexing.
- EXISTS/NOT EXISTS with a correlated subquery is often more efficient than IN/NOT IN, especially with NULLs involved.
- Query optimizers may rewrite correlated subqueries into semi-joins or anti-joins, but behavior varies by database engine.
- A correlated subquery cannot be logically evaluated independently of its outer query, unlike a plain subquery.
Practice what you learned
1. What distinguishes a correlated subquery from a regular (non-correlated) subquery?
2. Why can correlated subqueries be slower than JOINs on large tables?
3. Which pattern is generally preferred over correlated NOT IN when NULLs may be present in the subquery result?
4. In the query 'SELECT e1.name FROM employees e1 WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id)', what makes the inner subquery correlated?
Was this page helpful?
You May Also Like
Subqueries in SQL
A query nested inside another query, used in SELECT, FROM, or WHERE clauses to compute intermediate results.
INNER JOIN
Combine rows from two tables where the join condition matches in both, discarding unmatched rows.
Query Optimization Basics
Foundational techniques for writing queries the optimizer can execute efficiently.