Correlated Subqueries

A correlated subquery references the outer query. It runs once per row of the outer query.

Find employees who earn more than their department average:

SELECT first_name, salary, department_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

Notice e1.department_id in the subquery - that's the correlation. The subquery uses a value from the outer query.

Regular subquery: runs once, returns one result. Correlated subquery: runs once per outer row, can return different results each time.

Find each employee's rank without window functions:

SELECT first_name, salary,
    (SELECT COUNT(*) FROM employees e2
     WHERE e2.salary > e1.salary) + 1 AS rank
FROM employees e1;

Correlated subqueries are powerful but can be slow on large datasets. Window functions often replace them more efficiently.