Home/Learn/SQL/Correlated Subquery

Correlated Subquery

Intermediate

Subqueries & CTEs

A correlated subquery references columns from the outer query and re-executes once per outer row, enabling row-by-row comparisons at the cost of potential N+1 performance problems.

Overview

A correlated subquery differs from a regular subquery in that it contains a reference to a column from the outer query. The database engine cannot evaluate it independently — it must re-run the subquery for every row produced by the outer query. This makes correlated subqueries conceptually simple (the subquery "sees" the current outer row) but potentially very slow: an outer table with one million rows means the subquery runs one million times. EXISTS is the most efficient correlated subquery form because it short-circuits on the first match. For anything beyond an existence check, a JOIN or a window function is almost always faster. Modern optimisers can sometimes unnest correlated subqueries automatically, but this is not guaranteed.

Row-by-Row Execution and the Performance Risk

The correlated subquery sees each outer row and re-evaluates. This is powerful but can be catastrophically slow. Always check EXPLAIN to see whether the planner has unnested it.

SQL — correlated subquery, JOIN rewrite, window function rewrite

-- Find employees earning more than their own department's average salary
-- Correlated subquery version:
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees sub
    WHERE sub.department_id = e.department_id  -- correlated reference
);
-- For each of N employees, a separate AVG scan of that department.
-- O(N * dept_size) — bad on large tables.

-- Rewrite 1: JOIN to pre-aggregated subquery (one scan, then join)
SELECT e.name, e.salary, e.department_id
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
) dept ON dept.department_id = e.department_id
WHERE e.salary > dept.avg_sal;

-- Rewrite 2: window function (single pass, most idiomatic)
SELECT name, salary, department_id
FROM (
    SELECT
        name, salary, department_id,
        AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
    FROM employees
) ranked
WHERE salary > dept_avg;

EXISTS as an Efficient Correlated Subquery

EXISTS short-circuits — it stops scanning as soon as one matching row is found. Use it for existence checks instead of IN or COUNT(*) > 0.

SQL — EXISTS short-circuit, NOT EXISTS vs NOT IN

-- Find users who have placed at least one order
-- Anti-pattern: COUNT(*) — no short-circuit
SELECT u.id, u.name
FROM users u
WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 0;

-- Better: IN — materialises the subquery into a list
SELECT u.id, u.name
FROM users u
WHERE u.id IN (SELECT user_id FROM orders);

-- Best for existence check: EXISTS — short-circuits on first match
SELECT u.id, u.name
FROM users u
WHERE EXISTS (
    SELECT 1                         -- the value doesn't matter
    FROM orders o
    WHERE o.user_id = u.id
);

-- NOT EXISTS: find users with NO orders (safe against NULLs, unlike NOT IN)
SELECT u.id, u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Key Points to Remember

1A correlated subquery references an outer query column and re-executes once per outer row — an N+1 pattern in SQL.
2Rewrite correlated subqueries that aggregate data as a JOIN to a pre-aggregated subquery or as a window function.
3EXISTS short-circuits on the first match, making it the most efficient correlated subquery for existence checks.
4NOT EXISTS is NULL-safe; NOT IN returns no rows if the subquery contains any NULL.
5Check EXPLAIN to see whether the planner has automatically unnested (decorrelated) the subquery.

Interview Questions

Explain the performance difference between a correlated subquery and a window function for computing per-group averages.

HardGoogle

Write a query to find employees earning above their department average using both a correlated subquery and a window function.

MediumAmazon

Why is EXISTS preferred over COUNT(*) > 0 for existence checks?

EasySwiggy

What does it mean for a planner to "unnest" or "decorrelate" a correlated subquery?

HardNetflix

Ask Aria about Correlated Subquery

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Scalar Subquery

IN vs EXISTS vs ANY vs ALL