Correlated Subqueries & EXISTS / NOT EXISTS

When the inner query depends on the outer row

A correlated subquery is the opposite of the last lesson's regular subquery: its inner query references a column from the outer query's current row, so it cannot run once up front — it re-executes for every outer row. That re-execution is both its cost (slow on large tables) and its whole point: it lets you compute a value that *changes per row*, such as "the average for *this* row's group."

Processing order: outer query picks a row → inner query runs using a value from that row → outer query uses the result to decide whether to keep the row → repeat for the next row.

Worked example: rows above their own product's average (exam 6-80)

*Question:* list each order line whose quantity exceeds the average quantity for that same product.

The comparison value (the product's average) is different for every product, so a single up-front subquery can't supply it. The inner query must see the outer row's product_id:

SELECT ol.order_id, ol.product_id, ol.qty
FROM   order_lines ol
WHERE  ol.qty > (
        SELECT AVG(ol2.qty)
        FROM   order_lines ol2
        WHERE  ol2.product_id = ol.product_id);   -- ← correlation to the outer row

The line ol2.product_id = ol.product_id is the correlation: for each outer order line, the inner query averages only the rows for *that* product, and the outer WHERE keeps the line if its quantity beats that per-product average.

Worked example: hired before the latest hire in their state (exam 6-83)

Same shape, with MAX:

SELECT e.name, e.state, e.hired
FROM   employees e
WHERE  e.hired < (
        SELECT MAX(e2.hired)
        FROM   employees e2
        WHERE  e2.state = e.state)
ORDER BY e.state, e.hired;

For each employee, the inner query finds the most recent hire date in that employee's state; the outer keeps everyone hired strictly earlier — i.e. everyone except the single most-recently-hired person per state.

EXISTS and NOT EXISTS

EXISTS (subquery) is a pure presence test: it is true the moment the subquery returns *at least one row*, false if it returns none. The subquery's *columns* are irrelevant, so the convention is SELECT *. EXISTS subqueries are almost always correlated — they reference the outer row to ask "does a related row exist?".

NOT EXISTS is the clean way to write "which X has no related Y" — and unlike NOT IN, it is null-safe.

-- Instructors who have never taught a section:
SELECT i.name
FROM   instructors i
WHERE  NOT EXISTS (
        SELECT *
        FROM   sections s
        WHERE  s.instr_id = i.instr_id)
ORDER BY i.name;

For each instructor the inner query checks whether *any* section names them; NOT EXISTS keeps those with none. Result: Dr. Rossi, Dr. Ueda, Dr. Vidal.