What is the difference between a subquery, a derived table and a CTE?
Quick Answer
A subquery is a query nested in a SELECT/WHERE/HAVING. A derived table is a subquery in the FROM clause with an alias. A CTE is a named subquery declared with WITH before the statement. All three are logically equivalent and the optimizer generally treats them the same — the choice is readability.
They're the same concept in different positions. A subquery is nested inside a SELECT or a WHERE. A derived table is a subquery sitting in the FROM clause with an alias, like an inline view. A CTE is the same thing but named and declared up front with WITH. Logically they're interchangeable and the optimizer usually produces the same plan, so I pick based on readability — a CTE when I want to name the steps or reference it twice, a derived table for a quick one-off. The thing to know is none of them are cached — reference an expensive CTE multiple times and it can be evaluated multiple times.
- CTE wins for readability (name each step, reference it more than once, recursion).
- Derived table is fine for a one-off inline transformation.
- None of them are “materialized” by default — if you reference an expensive CTE five times it may run five times; a
#temptable runs it once. - Correlated subqueries in the SELECT list can be a performance trap on large result sets.