Lesson 20 — Correlated subquery

Correlated subqueries: a subquery per row

A correlated subquery refers to the outer row, so it is re-evaluated for each row. "Employees who earn more than the average of their own department" needs a different average for each person — the subquery filters by the outer row's department.

SELECT name FROM staff s
WHERE salary > (SELECT AVG(salary) FROM staff WHERE dept = s.dept)

Here s is the alias of the outer table, and s.dept is the current row's department.

Why it matters: "above their group's average", "latest order per customer" — per-row comparisons are everyday analytics.