Correlated Subqueries
Overview
A standard subquery executes exactly once, calculates its result, and passes it to the outer query. A Correlated Subquery is terrifyingly different. It contains a direct reference to a column in the outer query. This creates a loop. The database is forced to execute the inner query OVER AND OVER AGAIN—exactly once for every single row in the outer table. While powerful, they are notorious for causing catastrophic N+1 performance issues.
Syntax
-- A Correlated Subquery!
-- Notice how the INNER query references 'e1', which belongs to the OUTER query!
SELECT e1.first_name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary > (
-- This inner query runs repeatedly for EVERY employee!
-- It calculates the average salary for THAT specific employee's department.
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
);Common Pitfalls
- Accidental DoS (Denial of Service). If your outer
userstable has 1,000,000 rows, a Correlated Subquery will execute the inner query 1,000,000 separate times. This can literally bring down a production database. Modern optimizers (like Postgres) try to rewrite these into JOINs under the hood, but you should always prefer writing an explicitJOINor using Window Functions if possible. - Variable shadowing. If you forget to use table aliases (
e1ande2), the database gets confused about whichdepartment_idbelongs to the inner table vs the outer table, often resulting in silent logical failures.
Interview Questions
Execution frequency. A standard subquery evaluates independently, exactly once, from the bottom-up. A correlated subquery is dependent on the outer query, evaluating row-by-row in a top-down loop.
Real-World Example
Finding all employees who earn more than the average salary of their own specific department, using a modern JOIN to avoid the correlated performance penalty.
-- The Optimized approach (No Correlated Subqueries!)
SELECT e.first_name, e.salary, e.dept_id
FROM employees e
JOIN (
-- Pre-calculate ALL averages once!
SELECT dept_id, AVG(salary) as dept_avg
FROM employees
GROUP BY dept_id
) AS avg_table
ON e.dept_id = avg_table.dept_id
WHERE e.salary > avg_table.dept_avg;Check Your Knowledge
Test your understanding of Correlated Subqueries with these quick questions.