Common Table Expression (CTE) in SQL Server

We will use the SQL Server’s Common Table Expressions or CTEs to make complex joins and subqueries easier. It also provides a way to query hierarchical data, such as an organizational hierarchy. This article gives a complete overview of CTE, types of CTE, advantages, disadvantages, and how to use them in SQL Server.

What is CTE in SQL Server?

A CTE (Common Table Expression) is a one-time result set that only exists for the duration of the query. It allows us to refer to data within a single SELECT, INSERT, UPDATE, DELETE, CREATE VIEW, or MERGE statement’s execution scope. It is temporary because its result cannot be stored anywhere and will be lost as soon as a query’s execution is completed. It first came with SQL Server 2005 version. A DBA always preferred CTE to use as an alternative to a Subquery/View. They follow the ANSI SQL 99 standard and are SQL-compliant.

CTE Syntax in SQL Server

The CTE syntax includes a CTE name, an optional column list, and a statement/query that defines the common table expression (CTE). After defining the CTE, we can use it as a view in a SELECT, INSERT, UPDATE, DELETE, and MERGE query.

The following is the basic syntax of CTE in SQL Server:

WITH cte_name (column_names)   

AS (query)     

SELECT * FROM cte_name;

In this syntax:

• We have first specified the CTE name that will be referred to later in a query.
• The next step is to create a list of comma-separated columns. It ensures that the number of columns in the CTE definition arguments and the number of columns in the query must be the same. If we have not defined the CTE arguments’ columns, it will use the query columns that define the CTE.
• After that, we’ll use the AS keyword after the expression name and then define a SELECT statement whose result set populates the CTE.
• Finally, we will use the CTE name in a query such as SELECT, INSERT, UPDATE, DELETE, and MERGE statement.

It should keep in mind while writing the CTE query definition; we cannot use the following clauses:

1. ORDER BY unless you also use as TOP clause
2. INTO
3. OPTION clause with query hints
4. FOR BROWSE

The below image is the representation of the CTE query definition.

Here, the first part is a CTE expression that contains a SQL query that can be run independently in SQL. And the second part is the query that uses the CTE to display the result.

Example

Let us understand how CTE works in SQL Server using various examples. Here, we are going to use a table “customer” for a demonstration. Suppose this table contains the following data:

In this example, the CTE name is customers_in_newyork, the subquery that defines the CTE returns the three columns customer name, email, and state. As a result, the CTE customers_in_newyork will return all customers who live in New York State.

After defining the CTE customers_in_newyork, we have referenced it in the SELECT statement to get the details of those customers who are located in New York.

WITH customers_in_NewYork  

AS (SELECT * FROM customer WHERE state = 'New York')  

SELECT c_name, email, state FROM customers_in_NewYork;

After executing the above statement, it will give the following output. Here, we can see that the result returns only that customer information who are located in New York State.

Multiple CTE

In some cases, we’ll need to create multiple CTE queries and join them together to see the results. We may use multiple CTEs concept in this scenario. We need to use the comma operator to create multiple CTE queries and merge them into a single statement. The “,” comma operator must be preceded by the CTE name to distinguish multiple CTE.

Multiple CTEs help us in simplifying complex queries that are eventually joined together. Each complex piece had its own CTE, which could then be referenced and joined outside the WITH clause.

NOTE: The multiple CTE definition can be defined using UNION, UNION ALL, JOIN, INTERSECT, or EXCEPT.

The below syntax explains it more clearly

WITH   

   cte_name1 (column_names) AS (query),  

   cte_name2 (column_names) AS (query)  

SELECT * FROM cte_name  

UNION ALL  

SELECT * FROM cte_name;

Example

Let us understand how multiple CTE works in SQL Server. Here, we are going to use the above “customer” table for a demonstration.

In this example, we have defined the two CTE names customers_in_newyork and customers_in_california. Then the result set of subqueries of these CTEs populates the CTE. Finally, we will use the CTE names in a query that will return all customers who are located in New York and California State.

WITH   

customers_in_NewYork  

    AS (SELECT * FROM customer WHERE state = 'New York'),  

customers_in_California  

    AS (SELECT * FROM customer WHERE state = 'California')  

SELECT c_name, email, state FROM customers_in_NewYork  

UNION ALL  

SELECT c_name, email, state FROM customers_in_California;

New York and California State.

Why do we need CTE?

Like database views and derived tables, CTEs can make it easier to write and manage complex queries by making them more readable and simple. We can accomplish this characteristic by breaking down the complex queries into simple blocks that can reuse in rewriting the query.

Some of its use cases are given below:

• It is useful when we need to define a derived table multiple times within a single query.
• It is useful when we need to create an alternative to a view in the database.
• It is useful when we need to perform the same calculation multiple times on multiple query components simultaneously.
• It is useful when we need to use ranking functions like ROW_NUMBER(), RANK(), and NTILE().

Some of its advantages are given below:

• CTE facilitates code maintenance easier.
• CTE increases the readability of the code.
• It increases the performance of the query.
• CTE makes it possible to implement recursive queries easily.

Types of CTE in SQL Server

SQL Server divides the CTE (Common Table Expressions) into two broad categories:

1. Recursive CTE
2. Non-Recursive CTE

Recursive CTE

A common table expression is known as recursive CTE that references itself. Its concept is based on recursion, which is defined as “the application of a recursive process or definition repeatedly.” When we execute a recursive query, it repeatedly iterates over a subset of the data. It is simply defined as a query that calls itself. There is an end condition at some point, so it does not call itself infinitely.

A recursive CTE must have a UNION ALL statement and a second query definition that references the CTE itself in order to be recursive.

Example

Let us understand how recursive CTE works in SQL Server. Consider the below statement, which generates a series of the first five odd numbers:

WITH    

odd_num_cte (id, n) AS    

(    

SELECT 1, 1     

UNION ALL    

SELECT id+1, n+2 from odd_num_cte where id < 5     

)    

SELECT * FROM odd_num_cte;

When we execute this recursive CTE, we will see the output as below:

The below example is the more advanced recursive CTE. Here, we are going to use the “jtp_employees” table for a demonstration that contains the below data:

This example will display the hierarchy of employee data. Here table provides a reference to that person’s manager for each employee. The reference is itself an employee id within the same table.

WITH cte_recursion (EmpID, FirstName, LastName, MgrID, EmpLevel)  

  AS  

  (  

    SELECT EmployeeID, FirstName, LastName, ManagerID, 1  

    FROM jtp_employees WHERE ManagerID IS NULL  

    UNION ALL  

    SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.ManagerID, r.EmpLevel + 1  

    FROM jtp_employees emp INNER JOIN cte_recursion r ON emp.ManagerID = r.EmpID  

  )  

SELECT  

  FirstName + ' ' + LastName AS FullName, EmpLevel,  

  (SELECT FirstName + ' ' + LastName FROM jtp_employees   

    WHERE EmployeeID = cte_recursion.MgrID) AS Manager   

    FROM cte_recursion ORDER BY EmpLevel, MgrID

This CTE will give the following output where we can see the hierarchy of employee data:

Non-Recursive CTE

A common table expression that doesn’t reference itself is known as a non-recursive CTE. A non-recursive CTE is simple and easier to understand because it does not use the concept of recursion. According to the CTE Syntax, each CTE query will begin with a “With” clause followed by the CTE name and column list, then AS with parenthesis.

Disadvantages of CTE

The following are the limitations of using CTE in SQL Server:

• CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.
• The CTE can only be referenced once by the Recursive member.
• We cannot use the table variables and CTEs as parameters in stored procedures.
• We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.
• Since it’s just a shortcut for a query or subquery, it can’t be reused in another query.
• The number of columns in the CTE arguments and the number of columns in the query must be the same.