This article describes how you can use SQL Server CTE. It gives you detailed syntax, examples, benefits, and when to use CTE.
A Common Table Expression is a temporary result set that has a scope untill the execution of the query. It can be called a temporary table but the difference is that it does not capture any space in the metadata. The CTE is not stored anywhere and can be referred multiple times after declaration but within the same query.
WITH CTE_Name [Column1,Column2,…. ] AS <CTE Query> SELECT <Column1,Column2,…> FROM CTE_Name
CTE_Name is the name of the expression which will be created as a table. It is temporary and contains Column1, Column2… as column names derived from the query written under the CTE Query part. It is temporary and contains Column1, Column2… as column names derived from the query written under the CTE Query part.
The SELECT query uses CTE columns of query.Example 1:
This is a simple example to show the working of Common Table Expression. Here we will consider a table named StudentTotalMarks and we will get a percentage of students by using CTE.
SELECT * FROM StudentTotalMarks
The StudentTotalMarks table has above given data, now we need to calculate the percentage of all students by using Common Table Expression.
WITH MarksCTE(StudentName,Percentage,TotalObtained) AS ( SELECT StudentName, (CAST(MarksObtained AS float)/ CAST(TotalMarks AS float)*100) Percentage, MarksObtained FROM StudentTotalMarks ) SELECT * FROM MarksCTE
It generates below output
The query here returns the percentage of all the students. We can also get this result by a simple query, however that would require creating a table and transferring the data into it. By using CTE we do not need to do so.
We can use any operation on CTE other than SELECT like INSERT, UPDATE, DELETE, JOIN, User Defined Functions.
A Common Table Expression is very much useful for removing duplicity which is the most common problem in handling the Database. Consider a table named EmployeeDuplicate which has duplicate values. We will remove duplicate values with the help of CTE.
SELECT * FROM EmployeeDuplicate
The above table has 2 duplicate rows, with EmpID 1001 and 1003.
WITH DuplicateCTE(EmpID,EmpName,Department,RowID) AS ( SELECT EmpID, EmpFirstname+''+EmpLastName as EmpName, Department, ROW_NUMBER()OVER(PARTITION BY EmpFirstname,EmpLastName, Department ORDER BY EmpID)AS RowID FROM EmployeeDuplicate ) DELETE FROM DuplicateCTE WHERE RowID>1 SELECT * FROM EmployeeDuplicate
After running this query we again check the records in EmployeeDuplicate table.
The above query provides the sequence number to the data on the basis of EmpID and then we delete records that have RowID greater than 1. You can generate sequence numbers using Row_number or Rank or Dense_rank functions.
A Common Table Expression can also be used in creating recursive query. Consider an example where we want to know the hierarchy of the company and which person reports to which manager. This can be done by using a CTE. Consider table EmployeeDetails which contains employees names as well as their managers in the same table, now we want to show the data in such a way that it exhibits hierarchy.
SELECT * FROM EmployeeDetails
Now write the below given Common Table Expression Query which uses UNION ALL to get details from EmployeeDetails and CTE.
WITH EmployeeCTE (EmpID, FirstName, LastName, ManagerID,Department, EmpLevel) AS ( SELECT EmpID, EmpFirstName, EmpLastName, ManagerID, Department, 1 FROM EmployeeDetails WHERE ManagerID IS NULL UNION ALL SELECT E.EmpID,E.EmpFirstName,E.EmpLastName,E.ManagerID, E.Department, C.EmpLevel + 1 FROM EmployeeDetails E INNER JOIN EmployeeCTE C ON E.ManagerID = C.EmpID ) SELECT FirstName ,LastName,EmpLevel,Department, (SELECT EmpFirstName + ' ' + EmpLastName FROM EmployeeDetails WHERE EmpID = EmployeeCTE.ManagerID) AS Manager FROM EmployeeCTE ORDER BY EmpLevel, ManagerID
It generates below output
The above query shows the result in the following pattern
You can use CTE for joining CTE output with Table output. See more details on Types of SQL Joins.
The below query uses the AdventureWorks database. If you do not have you may download AdventureWorks2017 database. It creates Common Table Expression with the name VendorCTE and then joins with the Vendor table. This CTE is useful to get sum of the TotalDue field for VendorIDs.
WITH VendorCTE AS (SELECT VendorID, SUM(TotalDue) AS TotalDue FROM Purchasing.PurchaseOrderHeader PH JOIN Purchasing.PurchaseOrderDetail PD ON PH.PurchaseOrderID = PD.PurchaseOrderID GROUP BY VendorID) SELECT V.AccountNumber, V.Name, VC.SumTotalDue FROM VendorCTE VC JOIN Purchasing.Vendor V ON V.BusinessEntityID = VC.VendorID ORDER BY TotalDue DESC
You can also join multiple Common Table Expressions as shown in below query
WITH OrderCTE ( CustomerID, CompanyName, ContactName, City, OrderID, OrderDate, ShippedDate) AS ( SELECT C.CustomerID, CompanyName, ContactName, City, OrderID, OrderDate, ShippedDate FROM Orders O JOIN Customers C ON O.CustomerID = C.CustomerID WHERE C.CustomerID = 'ALFKI' ), OrderDetailsCTE ( OrderID, ProductID, ProductName, UnitPrice, Quantity) AS ( SELECT OD.OrderID, P.ProductID, ProductName, OD.UnitPrice, Quantity FROM Products P JOIN [Order Details] OD ON P.ProductID = OD.ProductID ) SELECT * FROM OrderCTE O JOIN OrderDetailsCTE OD ON O.OrderID = OD.OrderID
If you compare the execution plan both the with CTE and without CTE will have a similar execution plan and tables get joined in the same order. The reason for it is CTE is not the table. It is only an SQL statement to state SELECT queries. The CTE does not executes or prepare an execution plan before the actual use with SELECT statements. It just increases readability and usability.