SQL Server Common Table Expressions

SQL Server is a relational database management system (RDBMS) that is used to handle complex data and maintain it in of tabular manner. With the help of SQL Server, one can easily protect their data as it provides various security features.

In this article, we are going to explore SQL server‘s CTE also known as Common Table Expressions. They are like virtual temporary tables that are created during the query execution and deleted once the query is executed completely. We will learn about the SQL Server CTE in depth manner along with its types and some examples with the practical implementations and so on.

What is SQL Server CTE?

The Common Table Expressions (CTE) were introduced in SQL Server back in 2005. It is a temporary named result set that can make complex queries simple to write and understand by breaking them into smaller chunks. The CTEs can be referenced with INSERT, UPDATE, DELETE, or SELECT statements in SQL. The CTE is a very effective tool when we are dealing with recursive queries. By recursive queries, we mean the queries that reference their output for further execution. They are also very useful when we are performing operations using the aggregate functions. We can first calculate the aggregated values in a CTE and then we can use them in the main query. Now that we have got understanding of CTE let’s explore its syntax and how it works.

Syntax of CTE:

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )

Explanation:

The syntax of CTE mainly consists of 3 components:

• expression_name: This is a valid identifier for the CTE. The expression name must be different from any other CTE defined in the same WITH <common_table_expression> clause.

• column_name: Declare a list of comma-separated columns. The number of columns must be the same as the number of columns define in the CTE_query_definition.

• CTE_query_definition: Specifies a SELECT statement whose result set populates the common table expression. The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE can’t define another CTE.

Syntax:

WITH CTE_Name (Column1, Column2, ...) AS (
    -- CTE Query
    SELECT ...
)

SELECT * FROM CTE_Name;

Explanation: Note: The CTE is defined using a SELECT statement and must be followed by a main query that refereneces the CTE.

Types of CTE in SQL Server

In SQL Server Common Table Expressions are mainly of two types.

1. Non Recursive CTE

Non Recursive Common Table Expression also known as simple CTE is mainly used to create a temporary result set for queries that are not much complex in nature and which involves iterative operations only. The Non Recursive CTE’s enhances the readability of the code by breaking down the complex queries into small chunks.

Syntax of Non Recursive CTE:

WITH cte_name (column1, column2, ...) AS
(
    -- CTE query definition
    SELECT column1, column2, ...
    FROM your_table
    WHERE condition
)

SELECT *
FROM cte_name;

2. Recursive CTE

Recursive Common Table Expression also known as hierarchical or iterative CTE is mainly used to create a temporary result set for queries that are complex in nature. These are very useful when we are executing our queries in the databases which consists of hierarchical data structures. These CTE’s refer to their own output until the query statisfies the required condition. To terminate the recursive query we use the WHERE condition.

You must follow some rules in order to define the recursive CTE.

Syntax of Recursive CTE:

WITH RecursiveCTE (column1, column2, ...) AS
(
    SELECT column1, column2, ...
    FROM your_table
    WHERE condition
    
    UNION ALL
    
    -- Recursive member
    SELECT column1, column2, ...
    FROM your_table
    WHERE condition
)

SELECT *
FROM RecursiveCTE;

Examples of CTE

To understand the CTE in SQL Server we need a table on which we will perform various operations. So here we have a Employees table which consists of EmployeeID, LastName, Department, ManagerID,and Salary.

After inserting some data into the Employees table, Our table looks:

Employees Table

Example 1: Simple (Non Recursive )CTE

Let’s create a simple Common table Expression named ITStaff which will be used to store data of specific columns from the Employee table. In this example we have selected 3 columns EmployeeId, FirstName and Salary from the table Employees which filters the data based on the Department column. With ItStaff AS clause will be used to create the CTE and the matching records based on the specified condition will be stored inside the ITStaff CTE.

Query:

WITH ITStaff AS
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Department = 'IT'
)
--Show the records stored inside  the CTE we created above
SELECT *
FROM ITStaff;

Output:

Output

Explanation: In the above query ITStaff CTE is a simple common table expression that is used to store a subset of records from the Employees table. The subset contains the information about employees working in the ‘IT‘ department. Then the SELECT * statement is used to fetch the records stored inside the CTE. In our Employees tables we had 3 employees who works in the IT department so those 3 employees are stored in the CTE and displayed in the result.

Example 2: Recursive CTE

In the following example we are going to see how we can create recursive common table expressions, a recursive CTE is very useful for querying hierarchical data such as organizational databases where multiple employees reports to a single manager. The example will mainly consist of 3 parts:

1. CTE Definition: It it the initial part of the query where we will define the CTE using the Select statment. The Select statement will be used to select the required columns and UNION ALL clause will be used to combine the initial set of records with the recursive part.

2. Recursive Part: In this part the main logic for the recursion will be executed. JOINS will be performed so that the queries can refer to their own outputs for further execution to get better results.

3. Main query: This will be the final part of the query where SELECT * clause will be used to fetch the results stored inside the CTE.

Query:

WITH RecursiveEmployeeCTE AS
(
    SELECT EmployeeID, FirstName, LastName, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
   
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID
    FROM Employees e
    JOIN RecursiveEmployeeCTE r ON e.ManagerID = r.EmployeeID
)
--Show the records stored inside  the CTE we created above
SELECT *
FROM RecursiveEmployeeCTE;

Output:

Output

Explanation: In the above query a recursive common table expressions RecursiveEmployee CTE is created to fetch the hierarchical structure of employees and their managers temporarily. The CTE starts by selecting employees from the table who have no manager i.e the employees whose ManagerID is NULL. Then the UNION ALL clause is used to combine the result of the initial query with the recursive part. Then a SELF JOIN is performed to fetch the remaning of the employees whose ManagerID is not NULL.

Example 3: CTE for Aggregation (Count)

In this example we will learn how we can use the aggregate function COUNT() in our common table expression. The Aggregate functions performs calculations on multiple values and return a single value. Here we will use the Aggregate function COUNT() to find out the number of employees working in each department and then the result will be stored in a CTE DepartmentEmployeeCountCTE.

Query:

//Aggregate CTE for Department Employee Count

WITH DepartmentEmployeeCountCTE AS
(
    SELECT Department, COUNT(*) AS EmployeeCount
    FROM Employees
    GROUP BY Department
)

//Show the records stored inside  the CTE we created above
SELECT *
FROM DepartmentEmployeeCountCTE;

Output:

Output

Explanation: In the above query a DepartmentAvgCount CTE is created that calculates the count of employees department wise. The aggregate function Count is used along with the GROUP BY clause in order to fetch the employee count.

Example 4: CTE for Aggregation (Average)

In this example we will learn how we can use the Aggregate functions AVG() in our common table expression. Here we will use theAggregate functions AVG() to find out the average salary of employees in each department and then the result will be stored in a CTE DepartmentAvgSalaryCTE.

Query:

//Aggregate CTE for Department's Average Salary

WITH DepartmentAvgSalaryCTE AS
(
    SELECT Department, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY Department
)

--Show the records stored inside  the CTE we created above
SELECT *
FROM DepartmentAvgSalaryCTE;

Output:

Output

Explanation: In the above query a DepartmentAvgSalaryCTE is created that to find out the average salary of employees in each department The aggregate function AVG() is used along with the GROUP BY clause to group the employees based on their departments.

Conclusion

After reading overall article, now we have good understanding of SQL Server CTE along with its type Recursive and Non- Recursive. Also we have seen some example or queries with the practical implementations. Generally the CTE is used to enhance code clarity and maintainability. It also offers performance improvement and flexibility.