Correlated Subqueries
Overview
A Correlated Subquery is a nested query that depends on columns from the outer query, evaluating once for each row processed by the outer query.
Learning Objectives
- Distinguish correlated subqueries from independent subqueries.
- Reference outer query aliases within nested subqueries.
- Identify performance bottlenecks and rewrite correlated subqueries using JOINs or Window Functions.
Detailed Concept Explanation
Unlike independent subqueries (which execute once and pass their result to the outer query), a correlated subquery references columns from the outer query's row context.
Because the subquery relies on the current outer row, the database engine conceptually re-evaluates the inner subquery for every single row processed by the outer query ($O(N \times M)$ complexity).
Code Examples
SQL
-- Find employees earning more than the average salary of THEIR OWN department
SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e1.department_id -- References outer alias e1!
);
Best Practices
- Performance Optimization: Correlated subqueries can be slow on large tables. When performance matters, consider rewriting them using
JOINs with aggregated subqueries orWINDOWfunctions (AVG(...) OVER (PARTITION BY ...)).
Interview Perspective
Danger
Performance Benchmark: Correlated Subquery vs Window Function
Interviewers love asking how to optimize correlated subqueries.
Correlated Subquery ($O(N \times M)$):
WHERE salary > (SELECT AVG(salary) FROM emp e2 WHERE e2.dept = e1.dept)
Optimized Window Function ($O(N \log N)$):
WITH dept_avg AS (
SELECT name, salary, AVG(salary) OVER(PARTITION BY dept) as avg_sal FROM emp
)
SELECT * FROM dept_avg WHERE salary > avg_sal;
Interactive Challenges
Summary
Correlated subqueries evaluate once for every outer row by referencing outer columns. Optimize them using window functions when handling large tables.
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.