SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries

Part 12 of 18 · ~2 min

Correlated Subqueries

The scalar subquery two paragraphs up never once glanced at the outer query — it computed the whole-company average by itself and handed that single number to every row indiscriminately. A correlated subquery breaks that independence on purpose: buried somewhere inside it is a reference back to a column from the outer query, so the inner query can no longer be answered in isolation — its output shifts depending on which row the outer query happens to be sitting on at that moment. Try lifting it out and running it on its own and it errors out immediately, because "the row we're on right now" is a concept that only means something while the outer query is actively running.

Slide three's real headline is "who's the top earner on their own team," which needs exactly this:

-- For each employee, is their salary the highest 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: this line ties the inner query to whichever outer row we're on
);
-- Eve (HR, 55000), Carol (IT, 70000), Bob (Sales, 60000)

Set the two side by side and the contrast is exactly this: the earlier AVG(salary) subquery mentions nothing outside itself and computes one number, period. This one's inner WHERE e2.dept = e.dept changes meaning on every pass, because e.dept is a different department each time the outer query lands on a new employee — five outer employees, up to five distinct MAX calculations, one per department actually visited. The mental model worth building first, before worrying about speed, is that the inner query genuinely re-runs per outer row. Whether the engine literally does that or quietly rewrites the whole thing into a join under the hood is an implementation detail real optimizers do take advantage of — but reasoning it out the slow way first is what tells you the answer is correct, independent of however fast the engine gets there.

Notice, too, who's not in the result: Dave. MAX(salary) for IT correctly ignores his NULL and lands on Carol's $70,000 — but then e.salary = 70000 is being asked of Dave's row too, and NULL = 70000 is UNKNOWN, not TRUE. He can't be the top earner and he can't be compared against the top earner either; he simply never clears the bar, in either direction, for the same reason he never showed up in the self-join two chapters back. One thing worth flagging before this pattern becomes a reflex: a correlated subquery is elegant to write and easy to misjudge the cost of, because "re-run once per outer row" scales however badly it sounds once the outer table stops being five rows. Look at an actual plan before assuming one performs fine on the real dataset (see Understanding SQL Indexes and Query Performance).