What is the difference between a correlated and an uncorrelated subquery?
Quick Answer
An uncorrelated subquery is independent — it can run on its own, once, and its result is used by the outer query. A correlated subquery references a column from the outer query, so conceptually it re-evaluates for each outer row. Correlated subqueries in a SELECT list can be slow over large result sets; a join or window function is often faster.
An uncorrelated subquery stands alone — you could run it by itself, it produces a result, and the outer query uses it; it's evaluated once. A correlated subquery mentions a column from the outer query inside it, so logically it has to run again for every row the outer query processes. That's fine for EXISTS because it stops at the first match, but a correlated scalar subquery in the SELECT list — “for each product, the average price of its category” — can be expensive, and you'd usually rewrite it with a window function or a join to a pre-aggregated derived table.
- Uncorrelated:
WHERE CategoryId IN (SELECT Id FROM Categories WHERE IsActive = 1). - Correlated:
WHERE Price > (SELECT AVG(Price) FROM Products p2 WHERE p2.CategoryId = p.CategoryId). - The optimizer can sometimes de-correlate into a join, but not always — check the plan.
EXISTSis a correlated subquery and is usually efficient because it short-circuits.