PrismitaLEARN · PRACTICE · GROW

What is a Common Table Expression (CTE), and when would you use one?

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

Quick Answer

A CTE is a named, temporary result set defined with WITH that exists only for the following statement. It makes complex queries readable by naming intermediate steps, supports recursion (hierarchies, org charts, bill of materials), and can be referenced multiple times in one statement.

A CTE is a named temporary result set you declare with WITH right before the query that uses it. The main reason I use one is readability — instead of a subquery nested inside a subquery, I name each step. It also does recursion, which is how you query a hierarchy like an org chart or a category tree. One thing to be clear on: a non-recursive CTE isn't a performance trick, the optimiser just inlines it like a subquery. If I'm referencing an expensive CTE several times, a temp table that materialises once can actually be faster.

  • Readability: break a nested query into named layers instead of subqueries three levels deep.
  • Recursion: an anchor member UNION ALL a recursive member that references the CTE — walk a parent/child tree.
  • Not a performance feature: a non-recursive CTE is usually inlined by the optimiser — it's the same as a derived table or subquery, not a materialised temp table. If you reference a CTE many times and it's expensive, a #temp table (materialised once) may be faster.
  • A CTE only lives for the single statement immediately after it.

SQL — recursive CTE for a hierarchy 
WITH OrgChart AS (
    SELECT EmployeeId, ManagerId, Name, 0 AS Level
    FROM   dbo.Employees WHERE ManagerId IS NULL      -- anchor
    UNION ALL
    SELECT e.EmployeeId, e.ManagerId, e.Name, oc.Level + 1
    FROM   dbo.Employees e
    JOIN   OrgChart oc ON e.ManagerId = oc.EmployeeId -- recursive
)
SELECT * FROM OrgChart ORDER BY Level, Name;
All SQL Server questions More on T-SQL