A correlated subquery references a column from the outer query — it can't be run on its own, because its result depends on which outer row is currently being evaluated. Conceptually, the engine re-runs it (or an equivalent) once per outer row, though real optimizers often rewrite it into a join internally for performance:
-- For each employee, is their salary the maximum in their own department?
SELECT e.name, e.dept, e.salary
FROM Employees e
WHERE e.salary = (
SELECT MAX(e2.salary)
FROM Employees e2
WHERE e2.dept = e.dept -- correlated: references the outer row's dept
);
Contrast this with the uncorrelated scalar subquery a few sections up (SELECT AVG(salary) FROM Employees, with no reference to the outer query) — that one computes a single company-wide value once, while this one conceptually computes a different value for every department. Correlated subqueries are expressive but can be a real performance trap on large tables if the engine can't rewrite them efficiently; always check the execution plan (see Understanding SQL Indexes and Query Performance) before assuming one is fine at scale.