SQL Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary named result set that exists only for the single SQL statement immediately following it. CTEs are created using the WITH keyword and are commonly used to simplify complex queries.

Basic Syntax

WITH CteName AS
(
    SELECT column1,
           column2
    FROM Customers
)
SELECT *
FROM CteName;

The CTE behaves much like a temporary table, except it is never physically stored in the database. Once the query has finished executing, the CTE disappears automatically.

Simple Example

Suppose we want to calculate how much each customer has spent before filtering the results.

WITH CustomerTotals AS
(
    SELECT
        CustomerId,
        SUM(OrderTotal) AS TotalSpent
    FROM Orders
    GROUP BY CustomerId
)
SELECT *
FROM CustomerTotals
WHERE TotalSpent > 1000;

The CTE performs the aggregation first. The outer query simply filters the already calculated totals.

Why Use a CTE?

• Makes large queries easier to read.
• Breaks complicated logic into smaller steps.
• Avoids deeply nested subqueries.
• Allows multiple logical stages within a single query.
• Supports recursion for hierarchical data.

Using Multiple CTEs

Several CTEs can be declared together by separating them with commas. Each CTE can reference any CTE declared before it.

WITH SalesTotals AS
(
    ...
),
TopCustomers AS
(
    SELECT *
    FROM SalesTotals
    WHERE TotalSpent > 1000
)
SELECT *
FROM TopCustomers;

Recursive CTEs

A recursive CTE is simply a CTE that references itself. They're commonly used for traversing hierarchical data such as:

• Employee / Manager structures
• Folder trees
• Product categories
• Organisation charts
• Bill of materials

The Two Parts of a Recursive CTE

1. Anchor Query – the starting rows.
2. Recursive Query – repeatedly finds the next level.

WITH EmployeeHierarchy AS
(
    -- Anchor Query

    SELECT ...

    UNION ALL

    -- Recursive Query

    SELECT ...
    FROM Employees
    JOIN EmployeeHierarchy
        ON ...
)
SELECT *
FROM EmployeeHierarchy;

Employee Hierarchy Example

EmployeeId  EmployeeName  ManagerId
----------  ------------  ---------
1           Sarah         NULL
2           James         1
3           Rebecca       1
4           Tom           2
5           Emily         2

Sarah manages James and Rebecca. James manages Tom and Emily. A recursive CTE can return the entire hierarchy regardless of its depth.

WITH EmployeeHierarchy AS
(
    SELECT
        EmployeeId,
        EmployeeName,
        ManagerId,
        0 AS Level
    FROM Employees
    WHERE ManagerId IS NULL

    UNION ALL

    SELECT
        e.EmployeeId,
        e.EmployeeName,
        e.ManagerId,
        eh.Level + 1
    FROM Employees e
        INNER JOIN EmployeeHierarchy eh
            ON e.ManagerId = eh.EmployeeId
)
SELECT *
FROM EmployeeHierarchy;

How the Recursion Works

1. The anchor query returns Sarah.
2. The recursive query finds everyone reporting to Sarah.
3. It then repeats using those employees.
4. The process continues until no more rows are returned.

MAXRECURSION (SQL Server)

SQL Server limits recursive CTEs to 100 levels by default to prevent infinite loops.

OPTION (MAXRECURSION 200)

Setting the value to 0 removes the limit completely. Only do this if you're certain your hierarchy cannot contain circular references.

Generating Numbers

Recursive CTEs aren't just for hierarchies—they can also generate sequences.

WITH Numbers AS
(
    SELECT 1 AS NumberValue

    UNION ALL

    SELECT NumberValue + 1
    FROM Numbers
    WHERE NumberValue < 10
)
SELECT *
FROM Numbers;

CTE vs Temporary Table

Summary

• CTEs improve readability.
• They simplify complex SQL.
• They only exist for one statement.
• Multiple CTEs can be chained together.
• Recursive CTEs allow SQL to navigate hierarchical data.
• Always ensure recursive queries have a termination condition.