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 or WINDOW functions (AVG(...) OVER (PARTITION BY ...)).

Interview Perspective

WHERE salary > (SELECT AVG(salary) FROM emp e2 WHERE e2.dept = e1.dept)

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.