What is CTE
The SQL Server CTE, also called as Common Table Expressions is used to generate a temporary named set (like temporary table) that exists for the duration of a query. We can define this CTE within the execution scope of single SELECT, INSERT, DELETE, or UPDATE statement.
SQL Server CTE Syntax
The basic syntax behind the CTE in SQL Server 2014 is as shown below:
|
1 2 3 4 5 6 7 8 |
-- 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 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 a valid and unique Column names because 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 own query
The basic rules to use this SQL Server CTE are:
- A CTE must be followed by a single SELECT, INSERT, DELETE, or UPDATE statement that use all or some of the CTE 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)
- We cannot reference the tables on remote server.
- Within the CTE Definition you cannot use following clause:
- You cannot use ORDER BY Clause unless you are using the TOP Clause
- INTO, FOR BROWSE, and OPTION clause with query hint.
In this article we will show you, How to write a CTE in SQL Server with example. For this 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 have eight records.
SQL Server CTE Example
In this simple example we will show you, How to write a simple CTE in SQL Server.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Example for SQL CTE 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 the CTE we are performing the Aggregations (SUM) on Numeric Columns (Yearly Income, and Sales) by Grouping Occupation, and Education. I suggest you to refer SQL GROUP BY Clause article to understand the grouping concept.
|
1 2 3 4 5 6 |
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 th records returned by the Total_Sale CTE
|
1 |
SELECT * FROM Total_Sale |
SQL Server CTE Example – Column Names
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 will show you, How to add column names to CTE in SQL Server.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Example for SQL CTE USE [SQL Tutorial] GO 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 error for the following two statements because they don’t have Alias column names. So, CTE will assign Income and Sale as their Column names.
|
1 2 |
,SUM([YearlyIncome]) -- No Alias Column Name ,SUM([Sales]) -- No Alias Column Name |
Select First Row in Each Group using SQL Server CTE
In this simple example we will show you, How to select the First row in each group using CTE in SQL Server.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
-- 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
Following statement will assign unique row numbers to each and every row present in the Employee table. Please refer ROW_NUMBER and ORDER BY Clause to understand the below query.
|
1 2 |
,ROW_NUMBER() OVER (PARTITION BY [Occupation] ORDER BY [YearlyIncome] DESC) AS Number |
Multiple SQL Server CTEs Example
In this simple example we will show you, How to use Multiple CTEs in one WITH statement. As we said earlier, we can write multiple CTE statements in one WITH statement by separating them with Comma.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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 SQL LEFT JOIN to understand the left outer join.
OUTPUT
Recursive CTE in SQL Server Example
The following are the basic guidelines to define a recursive CTE:
- The recursive common table expressions must contains at least two CTE query Definitions, a recursive member, and an anchor member.
- You can use any of the Set operator: UNION, UNION ALL, EXCEPT, or INTERSECT to combine the anchor members. UNION ALL operator is the only operator used for combining multiple recursive members.
- Th number of columns, and the data types should be 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.
In this example we will show you, How to write a Recursive CTE in SQL Server with example. For this demonstration, We are going to use [Employee table] table present in our [SQL Tutorial] Database.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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
Use Recursive CTE in SQL to display Hierarchical List
In this example we are going to modify the above example to show the hierarchical level visually.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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
Thank You for Visiting Our Blog
Leave a Reply