What is Common Table Expression in SQL Server?
A Common Table Expression is a temporary result set which has a scope till the execution of the query.
It can be called as a temporary table but 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.
Advantages of using Common Table Expression
- After declaration of table you can refer the table created multiple times in scope of the same query.
- Can be used in place of views or temporary tables.
- Recursive query is easily created through CTE.
Syntax of CTE
WITH CTE_Name [Column1,Column2,…. ]
CTE_Name is name of the expression which will be created as table.
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.
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 percentage of students by using CTE.
SELECT * FROM StudentTotalMarks
The StudentTotalMarks table has above given data, now we need to calculate percentage of all students by using Common Table Expression.
(CAST(MarksObtained AS float)/ CAST(TotalMarks AS float)*100) Percentage,
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 a 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
EmpFirstname+''+EmpLastName as EmpName,
ROW_NUMBER()OVER(PARTITION BY EmpFirstname,EmpLastName,
Department ORDER BY EmpID)AS RowID
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 which have RowID greater than 1.
You can generate sequence numbers using Row_number or Rank or Dense_rank functions.
CTE with recursive query
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.
EmployeeCTE (EmpID, FirstName, LastName, ManagerID,Department, EmpLevel)
SELECT EmpID, EmpFirstName, EmpLastName, ManagerID, Department, 1
WHERE ManagerID IS NULL
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 EmpFirstName + ' ' + EmpLastName FROM EmployeeDetails
WHERE EmpID = EmployeeCTE.ManagerID) AS Manager
ORDER BY EmpLevel, ManagerID
It generates below output
The above query shows the result in following pattern
- It finds for the person who does not have a manager, so the EmpLevel of that person is marked as 1, EmpID of this person is 1005.
- Now the query again goes and checks for that person where ManagerID of the person matches with EmpID in point 1. Here we get 3 employees 1003, 1004 and 1006 which has manager 1005.
- Also the EmpLevel counter is increased to 2.
- The query goes recursively until all the employees are checked.
CTE with Joins
You can use CTE for joining CTE output with Table output. See more details on Types of SQL Joins.
Below query uses AdventureWorks database. If you do not have you may download AdventureWorks2012 database.
It creates Common Table Expression with name VendorCTE and then joins with Vendor table. This CTE is useful to get sum of 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
( CustomerID, CompanyName, ContactName, City, OrderID, OrderDate, ShippedDate)
SELECT C.CustomerID, CompanyName, ContactName, City, OrderID, OrderDate, ShippedDate
FROM Orders O JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE C.CustomerID = 'ALFKI'
( OrderID, ProductID, ProductName, UnitPrice, Quantity)
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 similar execution plan and tables get joined in same orders. Reason for it
is CTE is not the table. It is only an SQL statements to state SELECT queries. CTE do not executes or prepare execution plan before the actual use with SELECT statements.
It just increases readability and usability.
When to use CTE
- When you need a recursive query where it gets a advantage of being reference itself. A recursive CTE get executed repeatedly. See an employee example above.
- It can be use when you want to use View like functionality but do not want to create metadata.
- When you want to refer resulting table output multiple times in same statement.
- Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
Download Source code