SQL Server CTE - What is Common Table Expressions or CTE in SQL

The SQL Server CTE also called Common Table Expressions. This SQL CTE is used to generate a temporary named set (like a temporary table) that exists for the duration of a query. We can define this SQL Server CTE within the execution scope of a single SELECT, INSERT, DELETE, or UPDATE statement.

The basic rules to use this SQL Server CTE are:

The SQL Server CTE must follow by a single SELECT, INSERT, DELETE, or UPDATE statement that uses all or some of the Common table expression columns.
Using UNION, UNION ALL, INTERSECT, or EXCEPT, we can define the multiple CTE definition.
A CTE can be referenced itself, and also previously defined CTEs but it cannot reference the next CTE (forward referencing)
In SQL common table expression, We cannot reference the tables on a remote server.
Within the CTE Definition, you cannot use the following clause:
1. You cannot use ORDER BY Clause unless you are using the TOP Clause
2. INTO, FOR BROWSE, and OPTION clause with query hint.

SQL Server CTE Syntax

The syntax behind the CTE in SQL Server is

-- CTE SQL Syntax
WITH Expression_Name (Column_Name1, ColumnName2,.....ColumnNameN)
AS
(CTE Dfinition) -- Write a query

--Using SQL CTE
SELECT Column_Name1, ColumnName2,.....ColumnNameN
FROM Expression_Name -- Or, CTE Name

Arguments of Sql common table expression are:

Expression_Name: Please specify a valid and unique name to the Common Table Expressions that you want to create. It must be different from any other CTE name defined in the same WITH.
Column_Name1, ColumnName2,…..ColumnNameN: Please specify valid and unique Column names. Because SQL Server CTE won’t allow any duplicate names. The number of columns that you specify here should match the result set of the CTE Definition
CTE Definition: Write your query.

For this SQL Common Table Expressions demonstration, we are going to use [Employee table] and [Department] table present in our [SQL Tutorial] Database.

From the below figure you can observe that [Employee table] table have fourteen records

And [Department] table has eight records.

SQL CTE Example

In this simple example, we will show you how to write a simple CTE in SQL Server.

-- Example for CTE SQL
USE [SQL Tutorial]
GO
WITH Total_Sale 
AS
(
SELECT [Occupation]
      ,[Education]
      ,SUM([YearlyIncome]) AS Income
      ,SUM([Sales]) AS Sale
  FROM [Employee Table]
  GROUP BY [Education], [Occupation]
)
SELECT * FROM Total_Sale

OUTPUT

ANALYSIS

Within this SQL Server CTE, we are performing the Aggregations (SUM) on Numeric Columns (Yearly Income, and Sales) by Grouping Occupation, and Education. I suggest you refer GROUP BY Clause article.

SELECT [Occupation]
      ,[Education]
      ,SUM([YearlyIncome]) AS Income
      ,SUM([Sales]) AS Sale
  FROM [Employee Table]
  GROUP BY [Education], [Occupation]

Next, we are using the SELECT statement to select all the records returned by the Total_Sale CTE

SELECT * FROM Total_Sale

SQL CTE Column Names Example

It is always good practice to add Columns names to CTE. Because SQL will not throw any error even if you are using duplicate names inside your CTE Definition, or not giving any name to the column. In this simple example, we show how to add column names to SQL common table expression.

-- Example for SQL CTE
WITH Total_Sale (Profession, Qualification, Income, Sale)
AS
(
SELECT [Occupation]
      ,[Education]
      ,SUM([YearlyIncome]) -- No Alias Column Name
      ,SUM([Sales]) -- No Alias Column Name
  FROM [Employee Table]
  GROUP BY [Education], [Occupation]
)
SELECT * FROM Total_Sale

OUTPUT

ANALYSIS

If you are not using the Column Names, then SQL Server will throw an error for the following two statements because they don’t have Alias column names. So, CTE in SQL will assign Income and Sale as their Column names.

  ,SUM([YearlyIncome]) -- No Alias Column Name
  ,SUM([Sales]) -- No Alias Column Name

SQL Server CTE Select First Row in Each Group

Let us see how to select the First row in each group using CTE in SQL Server.

-- Example for SQL CTE
USE [SQL Tutorial]
GO
WITH Highest_Record AS
(
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,ROW_NUMBER() OVER (PARTITION BY [Occupation] 
                          ORDER BY [YearlyIncome] DESC) AS Number
      ,[Sales]
      ,[HireDate]
  FROM [Employee Table]
  )
-- It will select all the records from above CTE, whose Rank is 1 (Highest rank)
SELECT [FirstName] + ' ' + [LastName] AS [Full Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
FROM Highest_Record AS hr
-- If you omit this where clause, then all the records from CTE are selected.
WHERE hr.Number = 1

OUTPUT

ANALYSIS

The following statement in the above SQL common table expression will assign unique row numbers to each row present in the Employee table. Please refer to ROW_NUMBER and ORDER BY Clause to understand the below query.

 ,ROW_NUMBER() OVER (PARTITION BY [Occupation] 
                     ORDER BY [YearlyIncome] DESC) AS Number

Multiple CTE in SQL Server Example

In this example, we show you how to use Multiple CTE in one WITH statement. As we said earlier, we can write multiple CTE statements in one WITH statement by separating them with Comma.

USE [SQL Tutorial]
GO
WITH Highest_Record AS
(
SELECT Id
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,ROW_NUMBER() OVER (PARTITION BY [Occupation] 
                          ORDER BY [YearlyIncome] DESC) AS Number
      ,[Sales]
      ,[HireDate]
  FROM [Employee Table]
),
-- Use Comma to Seperate multiple CTEs - Highest_Record and Department_Name
Department_Names AS
(
SELECT *
  FROM [Department]
)
-- We are using LEFT Join to join both the CTEs
SELECT hr.[FirstName] + ' ' + hr.[LastName] AS [Full Name]
      ,hr.[Education]
      ,hr.[Occupation]
      ,dept.DepartmentName
      ,hr.[YearlyIncome]
      ,hr.[Sales]
      ,hr.[HireDate]
      ,hr.Number AS [Rank]
FROM Highest_Record AS hr
LEFT JOIN 
    Department_Names AS dept ON
	hr.Id = dept.id

TIP: Please refer LEFT JOIN to understand the left outer join.

OUTPUT

CTE Recursive Example

The following are the basic guidelines to define a recursive CTE in Sql Server:

The recursive common table expressions must contain at least two CTE query Definitions, a recursive member, and an anchor member.
You can use any of the Set operators: UNION, UNION ALL, EXCEPT, or INTERSECT to combine the anchor members of Sql common table expression. UNION ALL operator is the only operator used for combining multiple recursive members.
The number of columns and the data types should be the same in the anchor and recursive members.
You cannot use GROUP BY, SELECT DISTINCT, PIVOT, HAVING, TOP, LEFT JOIN, RIGHT JOIN, OUTER JOIN, Subqueries, and Scalar Aggregations inside the recursive members CTE Definition.

This example shows how to write a Recursive CTE in SQL Server with an example. For this demonstration, we are going to use the [Employee table] table present in our [SQL Tutorial] Database.

SQL CODE

USE [SQL Tutorial]
GO 	
WITH CTE_Recursion AS   
(  
    SELECT [EmployeeID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ManagerID]
     ,1 AS EmployeeLevel  
    FROM MyEmployees   
    WHERE ManagerID IS NULL  
    UNION ALL  
  SELECT emp.[EmployeeID]
      ,emp.[FirstName]
      ,emp.[LastName]
      ,emp.[Education]
      ,emp.[Occupation]
      ,emp.[YearlyIncome]
      ,emp.[Sales]
      ,emp.[ManagerID]
      ,EmployeeLevel + 1  
    FROM MyEmployees AS emp  
        INNER JOIN CTE_Recursion AS cte  
        ON emp.ManagerID = cte.EmployeeID   
)  
SELECT *  
FROM CTE_Recursion  
ORDER BY EmployeeLevel, ManagerID;  
GO

OUTPUT

Sql Server CTE Recursive to display Hierarchical List

In this example also, we are using the SQL CTE recursive. However, this time we are going to modify the above example to show the hierarchical level visually.

-- SQL Server CTE Example
USE [SQL Tutorial]
GO
WITH CTE_Recursion
AS 
(
SELECT  EmployeeID,  
        1 AS [Designation Level],
 CONVERT(varchar(255), FirstName + ' ' + LastName) AS Name,
 [DeptID]   
  FROM MyEmployees
  WHERE ManagerID IS NULL  
UNION ALL  
SELECT emp.EmployeeID,  
      [Designation Level] + 1,
      CONVERT(varchar(255), REPLICATE ('|    ' , [Designation Level]) +  
                                        emp.FirstName + ' ' + emp.LastName),
      emp.[DeptID]     
  FROM MyEmployees AS emp  
     JOIN CTE_Recursion AS cte 
           ON emp.ManagerID = cte.EmployeeID  
)  
SELECT cte.EmployeeID, 
       cte.[Designation Level], 
       cte.Name, 
       dept.DepartmentName  
FROM CTE_Recursion AS cte
  INNER JOIN
      Department AS dept
    ON  cte.DeptID = dept.id
ORDER BY [Designation Level]    
GO

OUTPUT