LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 2822 min read

Common Table Expressions (CTEs)

Learn how to write Common Table Expressions in SQL Server using the WITH clause, why they improve readability over nested subqueries, and how to write a simple recursive CTE.

Introduction

As queries grow more complex, deeply nested subqueries become hard to read and maintain. SQL Server offers a cleaner alternative: the Common Table Expression, or CTE, defined with the WITH keyword. A CTE lets you name a temporary result set and reference it, sometimes more than once, within a single statement. This lesson covers basic and recursive CTEs.

What You Will Learn
  • What a CTE is and how the WITH clause works.
  • Why CTEs are often more readable than nested subqueries.
  • How to define multiple CTEs in one query.
  • How to write a simple recursive CTE.

What is a CTE?

A Common Table Expression is a named, temporary result set defined at the start of a query using WITH. It exists only for the duration of the statement that follows it — it is not stored permanently like a view or a table. Think of it as giving a subquery a name and pulling it out to the top of your query for clarity.

Basic WITH Syntax

A CTE is defined with WITH, followed by a name, an optional column list, AS, and a query in parentheses. The main query immediately follows and can reference the CTE by name as if it were a table.

Basic CTE
WITH HighEarners AS (
    SELECT FirstName, Department, Salary
    FROM Employees
    WHERE Salary > 60000
)
SELECT FirstName, Department
FROM HighEarners
ORDER BY FirstName;
Result

Click Run to see what this code prints.

CTEs vs Nested Subqueries

The subquery example from the previous lesson can be rewritten as a CTE, making the logic easier to read top to bottom instead of inside-out.

Subquery Rewritten as a CTE
-- Nested subquery version
SELECT FirstName, Department, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);


-- Equivalent CTE version
WITH AvgSalary AS (
    SELECT AVG(Salary) AS AverageSalary
    FROM Employees
)
SELECT e.FirstName, e.Department, e.Salary
FROM Employees AS e, AvgSalary
WHERE e.Salary > AvgSalary.AverageSalary;
Result

Click Run to see what this code prints.

Readability, Not Performance

A CTE is primarily a readability tool — SQL Server generally optimizes a CTE similarly to an equivalent subquery. Use CTEs to make complex logic easier to follow, not because they are inherently faster.

Multiple CTEs in One Query

You can define more than one CTE in a single WITH clause by separating them with commas. Later CTEs can even reference earlier ones, letting you build up logic in clear, named stages.

Multiple CTEs
WITH DepartmentTotals AS (
    SELECT Department, SUM(Salary) AS TotalSalary
    FROM Employees
    GROUP BY Department
),
TopDepartments AS (
    SELECT Department, TotalSalary
    FROM DepartmentTotals
    WHERE TotalSalary > 60000
)
SELECT * FROM TopDepartments
ORDER BY TotalSalary DESC;
Result

Click Run to see what this code prints.

Recursive CTEs

A recursive CTE references itself, which is useful for hierarchical data such as an organizational chart or a category tree. It consists of an "anchor" query (the starting point) combined with UNION ALL and a "recursive" query that repeatedly references the CTE itself, until no new rows are produced.

Recursive CTE — Employee Hierarchy
CREATE TABLE OrgChart (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    ManagerID INT NULL
);


INSERT INTO OrgChart VALUES
    (1, 'Ananya (CEO)', NULL),
    (2, 'Rohit (VP Eng)', 1),
    (3, 'Divya (VP Sales)', 1),
    (4, 'Karan (Engineer)', 2),
    (5, 'Meera (Engineer)', 2);


WITH OrgHierarchy AS (
    -- Anchor: start with the top-level employee (no manager)
    SELECT EmployeeID, EmployeeName, ManagerID, 0 AS HierarchyLevel
    FROM OrgChart
    WHERE ManagerID IS NULL


    UNION ALL


    -- Recursive: join back to OrgHierarchy to find each person's reports
    SELECT o.EmployeeID, o.EmployeeName, o.ManagerID, h.HierarchyLevel + 1
    FROM OrgChart AS o
    INNER JOIN OrgHierarchy AS h ON o.ManagerID = h.EmployeeID
)
SELECT EmployeeID, EmployeeName, HierarchyLevel
FROM OrgHierarchy
ORDER BY HierarchyLevel, EmployeeID;
Result

Click Run to see what this code prints.

Guard Against Infinite Recursion

If the data forms a cycle, or the recursive step never terminates, a recursive CTE can loop endlessly. SQL Server defaults to a maximum recursion level of 100 and will raise an error if exceeded; you can adjust this with OPTION (MAXRECURSION n) if a deeper hierarchy is genuinely expected.

Common Mistakes

Avoid These Mistakes
  • Forgetting that a CTE only exists for the single statement that immediately follows it — it cannot be reused in a later, separate statement.
  • Omitting UNION ALL in a recursive CTE, which is required to combine the anchor and recursive parts.
  • Writing a recursive CTE without a terminating condition, risking infinite recursion.
  • Assuming CTEs are always faster than subqueries — they primarily improve readability, not guaranteed performance.

Best Practices

  • Use CTEs to break complex logic into clearly named, readable steps.
  • Reach for a recursive CTE specifically for hierarchical or tree-structured data.
  • Always include a clear anchor query and a well-defined stopping condition in recursive CTEs.
  • Name CTEs descriptively (e.g. HighEarners, DepartmentTotals) rather than generic names like Temp1.

Frequently Asked Questions

No. A CTE is scoped only to the single statement that follows its WITH clause. If you need reusable logic across many queries, a view or a temporary table is more appropriate.

A CTE is a named query definition that is not materialized as physical data; a temp table (e.g. #TempTable) stores actual rows in tempdb. Temp tables can be indexed and reused across multiple statements in the same session, while CTEs cannot.

You must use UNION ALL between the anchor and recursive parts of a recursive CTE — UNION (which removes duplicates) is not supported there.

No, except when combined with TOP. ORDER BY without TOP is not meaningful inside a CTE since the CTE itself does not guarantee row order — apply ORDER BY in the outer query instead.

Key Takeaways

  • A CTE is a named, temporary result set defined with WITH.
  • CTEs improve readability compared to deeply nested subqueries.
  • Multiple CTEs can be chained together, each referencing earlier ones.
  • Recursive CTEs use an anchor query, UNION ALL, and a self-referencing recursive query.
  • A CTE only exists for the single statement immediately following it.

Summary

CTEs give you a clean, readable way to structure complex queries, and recursive CTEs unlock hierarchical data traversal that would otherwise be very difficult to express in plain SQL. Next, you will learn about views — a way to save a query as a reusable, named object in the database.

Next Lesson →

Views