Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary named result set that you can reference within a single SQL statement. CTEs are supported in SQL Server 2005 and later. They can be used with SELECT, INSERT, UPDATE, DELETE, and CREATE VIEW statements.

Why Use CTEs?

Readability: CTEs help break down complex queries into smaller, logical units, making them easier to understand and maintain.
Recursion: CTEs are essential for writing recursive queries, which are used to query hierarchical data.
Simplicity: They can simplify queries that would otherwise require subqueries or temporary tables.
Reusability within a single statement: A CTE can be referenced multiple times within the same statement.

Syntax

The basic syntax for a CTE is as follows:


WITH cte_name (column1, column2, ...) AS
(
    -- SELECT statement that defines the CTE
    SELECT ...
)
-- SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement that references the CTE
SELECT ...
FROM cte_name
WHERE ...;

Basic Example

Let's find employees earning more than the average salary.

Example: Employees Earning Above Average


WITH AverageSalary AS
(
    SELECT AVG(Salary) AS AvgSalary
    FROM Employees
)
SELECT E.EmployeeName, E.Salary
FROM Employees AS E, AverageSalary AS ASal
WHERE E.Salary > ASal.AvgSalary;

Recursive CTEs

Recursive CTEs are used to query hierarchical data, such as organizational charts or bill of materials. A recursive CTE consists of two parts:

Anchor Member: The non-recursive part that returns the base result set.
Recursive Member: The part that references the CTE itself. It is executed repeatedly until it returns no rows.

The two members are combined using the UNION ALL operator.

Recursive CTE Syntax


WITH RecursiveCTE AS
(
    -- Anchor Member
    SELECT initial_value
    FROM your_table
    WHERE condition_for_anchor

    UNION ALL

    -- Recursive Member
    SELECT ...
    FROM your_table t
    JOIN RecursiveCTE r ON t.parent_id = r.child_id
    WHERE condition_for_recursion
)
SELECT *
FROM RecursiveCTE;

Recursive Example: Employee Hierarchy

Imagine an Employees table with EmployeeID and ManagerID columns. This CTE finds all direct and indirect reports for a specific manager.

Example: Employee Hierarchy


-- Assume EmployeeID 1 is the CEO
WITH EmployeeHierarchy AS
(
    -- Anchor member: Select the initial employee (e.g., CEO)
    SELECT EmployeeID, EmployeeName, ManagerID, 0 AS Level
    FROM Employees
    WHERE EmployeeID = 1

    UNION ALL

    -- Recursive member: Select direct reports of employees in the previous level
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees AS e
    INNER JOIN EmployeeHierarchy AS eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeName, Level
FROM EmployeeHierarchy
ORDER BY Level, EmployeeName;

Limitations and Considerations

A CTE must be immediately followed by a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement that references the CTE.
By default, a recursive CTE has a maximum recursion depth of 100. You can increase this limit using the MAXRECURSION option (e.g., OPTION (MAXRECURSION 500)). Setting it to 0 allows infinite recursion, but use with caution.
CTEs are not stored procedures; they are executed each time they are referenced.

For more advanced scenarios and detailed explanations, please refer to the official Microsoft SQL Server documentation.