PrismitaLEARN · PRACTICE · GROW

What is a correlated subquery?

SQL Server T-SQL Intermediate 1–2 Yrs2–3 Yrs3–5 Yrs SQL Query 1 min read

Quick Answer

A correlated subquery references a column from the outer query, so it's evaluated once per outer row (conceptually). A non-correlated subquery is independent and runs once. Correlated subqueries power EXISTS and per-row lookups, but a poorly-indexed one can be slow.

A correlated subquery is one that references a column from the outer query, so logically it re-evaluates for each outer row. A non-correlated subquery stands on its own and runs once. The classic correlated one is EXISTS — WHERE EXISTS (SELECT 1 FROM Lines l WHERE l.OrderId = o.OrderId) depends on each order. The optimiser usually turns them into joins under the hood, but it's worth knowing they can be slow if the correlation column isn't indexed.

  • Non-correlated: WHERE Total > (SELECT AVG(Total) FROM Orders) — runs once.
  • Correlated: WHERE EXISTS (SELECT 1 FROM Lines l WHERE l.OrderId = o.OrderId) — depends on o.OrderId.
  • The optimiser often rewrites correlated subqueries into joins, but with the right index they can also seek efficiently row by row.
  • Common uses: EXISTS/NOT EXISTS, a scalar lookup in the SELECT list, per-group filters.
All SQL Server questions More on T-SQL