Correlated Subqueries & NOT EXISTS

# Correlated Subqueries & NOT EXISTS Every subquery you've seen so far in this module runs **once**, on its own, independent of the outer query -- whether it sits in `SELECT`, `FROM`, or `WHERE`. That's a **non-correlated** subquery. There's a second kind that behaves very differently. ## What makes a subquery "correlated" A **correlated subquery** references a column from the *outer* query inside the inner query. Because of that reference, the inner query can't just run once and hand back a fixed value -- it has to re-run **once per row** of the outer query, using that row's value each time. ```sql SELECT i.InvoiceId, i.CustomerId, i.InvoiceDate, i.Total FROM Invoice i WHERE i.Total > ( -- correlated: this inner query re-evaluates per outer row `i`, -- because it filters by i.CustomerId from the OUTER row SELECT AVG(i2.Total) FROM Invoice i2 WHERE i2.CustomerId = i.CustomerId ) ORDER BY i.CustomerId, i.InvoiceDate; ``` Compare this to a non-correlated scalar subquery like `(SELECT AVG(Total) FROM Invoice)` -- that one computes a single company-wide average once, and every row is compared against the same number. The correlated version above computes a **different** average for every customer, because `i2.CustomerId = i.CustomerId` ties the inner query back to whichever outer row is currently being evaluated. The result: invoices that are unusually large *for that specific customer*, not large in absolute terms. ## EXISTS and NOT EXISTS `EXISTS` and `NOT EXISTS` are almost always used with a correlated subquery. Instead of returning a value to compare, they just ask a yes/no question: *does at least one matching row exist?* ```sql SELECT e.EmployeeId, e.FirstName || ' ' || e.LastName AS Name, e.Title FROM Employee e WHERE NOT EXISTS ( -- true when no row in the "inner" employee table reports to e SELECT 1 FROM Employee sub WHERE sub.ReportsTo = e.EmployeeId ) ORDER BY e.EmployeeId; ``` This returns every employee who has **nobody reporting to them** -- the correlated subquery checks, per employee, whether any other employee's `ReportsTo` points back at them. `NOT EXISTS` is the standard way to write an "anti-join": find rows in one table that have *no* match in another. A common pitfall: `SELECT 1` inside an `EXISTS`/`NOT EXISTS` subquery is intentional, not a mistake. Since `EXISTS` only cares whether a row comes back at all, the selected column(s) are irrelevant -- `SELECT 1` is a convention for "I don't care what this returns, only whether it returns anything." ## Recap - A subquery is **correlated** when it references a column from the outer query -- that reference forces it to re-run once per outer row, instead of once total. - Use a correlated subquery when the comparison value should be *different for each row* (e.g. "above average for this specific group," not "above the overall average"). - `EXISTS` / `NOT EXISTS` pair naturally with correlated subqueries to test for the presence or absence of matching rows -- `NOT EXISTS` is the standard anti-join pattern.