How to Write a Common Table Expression in SQL Server

Common Table Expressions (CTEs) are a powerful SQL Server feature that allows you to simplify complex queries. A CTE provides a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It makes your queries more readable and modular.

Syntax of a Common Table Expression

The basic syntax for a CTE in SQL Server is as follows:

WITH CTE_Name (Column1, Column2, ...)
AS
(
    SELECT Column1, Column2, ...
    FROM TableName
    WHERE condition
)
SELECT *
FROM CTE_Name;
                                

Example of Using a CTE

Let’s say you have a table of employees and their managers, and you want to find all employees who report directly to a specific manager. Here’s how you can write this query using a CTE:

WITH EmployeeCTE AS
(
    SELECT EmployeeID, Name, ManagerID
    FROM Employees
    WHERE ManagerID = 1
)
SELECT EmployeeID, Name
FROM EmployeeCTE;
                                

This CTE selects all employees that report to the manager with ID 1, and then the final SELECT statement retrieves those employees' IDs and names.

Advantages of Using CTEs

• Improved Readability: CTEs break down complex queries into simpler parts.
• Reusable: You can refer to a CTE multiple times within the same query.
• Recursion Support: CTEs can be recursive, making them ideal for hierarchical data such as organizational charts.

Recursive CTE Example

Here’s an example of how to use a recursive CTE to find all subordinates of a manager:

WITH RecursiveCTE AS
(
    SELECT EmployeeID, Name, ManagerID
    FROM Employees
    WHERE ManagerID = 1
    UNION ALL
    SELECT e.EmployeeID, e.Name, e.ManagerID
    FROM Employees e
    INNER JOIN RecursiveCTE r
    ON e.ManagerID = r.EmployeeID
)
SELECT EmployeeID, Name
FROM RecursiveCTE;
                                

This recursive CTE will retrieve all employees who report to manager 1, as well as all employees under those employees, in a hierarchical manner.

Conclusion

Common Table Expressions (CTEs) are an excellent tool for simplifying your SQL queries, improving readability, and enhancing performance. By using them for complex subqueries or recursive queries, you can write more modular and maintainable SQL code.