SQL Server CTE

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

1. 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.
2. We can define multiple definitions using UNION, UNION ALL, INTERSECT, or EXCEPT.
3. It can be referenced itself and also previously defined common table expressions, but it cannot reference the next one (forward referencing)
4. In common table expression, We cannot reference the tables on a remote server.
5. Within the Definition, you cannot use the following clause:
   1. You cannot use the 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 of the common table expressions or CTE is

WITH Expression_Name (Column_Name1, ColumnName2,.....ColumnNameN)
AS
(Define) -- Write a query

SELECT Column_Name1, ColumnName2,.....ColumnNameN
FROM Expression_Name -- Or, Name

Arguments of Sql common table expression are:

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

We use [Employee table] and [Department] table for this SQL Common Table Expressions demo.

And [Department] table has eight records.

CTE Example

This simple example will show you how to write a simple CTE.

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

Within this CTE, we perform Aggregations (SUM) on Numeric Columns (Yearly Income and Sales) by Grouping Occupation and Education. Therefore, 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

SELECT * FROM Total_Sale

CTE Column Names Example

It is always good practice to add Column names to SQL server CTE or common table expressions. Because it will not throw any error even if you are using duplicate names inside your Definition or not giving any name to the column. Here, we add column names to common table expression 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

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

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

Select First Row in Each Group

Let us see how to select the First row in each group.

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 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 are returned.
WHERE hr.Number = 1

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 query definitions or expressions in a single query of WITH statement. As we said earlier, we can write multiple common table expressions or CTE statements in one WITH statement by separating them with Comma.

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 Separate multiple expressions - Highest_Record and Department_Name
Department_Names AS
(
SELECT *
  FROM [Department]
)
-- We are using LEFT Join to join 
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 to the LEFT JOIN.

SQL Server CTE Recursive Example

The following are the basic guidelines to define recursive CTEs is:

• The recursive common table expression must contain at least two query Definitions, a recursive member and an anchor member.
• You can use any set operators: UNION, UNION ALL, EXCEPT, or INTERSECT to combine the anchor members of SQL recursive common table expression. However, UNION ALL operator is the only operator used for connecting 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 of the Definition.

This example shows how to write a Recursive CTE. We use the [Employee table] table for this recursive common table expression demonstration.

The SQL Server RECURSIVE common table expressions, CTE query returns the below result.

WITH 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 Recursion AS rec  
        ON emp.ManagerID = rec.EmployeeID   
)  
SELECT * FROM Recursion  
ORDER BY EmployeeLevel, ManagerID;

Recursive to display Hierarchical List

Here also, we use the recursive CTE query definition by modifying the above example to show the hierarchical level visually.

WITH 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 Recursion AS rec ON emp.ManagerID = rec.EmployeeID  
)  
SELECT rec.EmployeeID, rec.[Designation Level], rec.Name, dept.DepartmentName  
FROM Recursion AS rec
INNER JOIN Department AS dept ON  rec.DeptID = dept.id
ORDER BY [Designation Level]