Correlated Subqueries

Analogy categoryAnalogies

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

sql

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

sql

-- 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?

Subqueries in SQL

Common Table Expressions (CTEs)