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.
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?
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 EXISTSpair naturally with correlated subqueries to test for the presence or absence of matching rows --NOT EXISTSis the standard anti-join pattern.