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
What is the primary operational difference between a Standard Subquery and a Correlated Subquery?
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.