Correlated Subquery

Source data — Pattern 8: Subqueries

All queries run against the shared employees and departments tables (see schema.sql). This pattern introduces scalar, correlated, and multiple-row subqueries. Topics 47 and 48 demonstrate the EXISTS and NOT EXISTS operators, while Topics 49 and 50 introduce the ANY (SOME) and ALL comparison operators.

---

What it does

A correlated subquery references columns from the outer query. It executes once for every row processed by the outer query.

Problem

Find employees earning above their department's average salary.

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

Result