Correlated Subqueries

These subqueries reference columns from the outer query and are evaluated once per row.

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

This compares each employee's salary to their department's average salary.