SQL Server Common Table Expression with Examples

What is Common Table Expression in SQL Server?

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.

Advantages of using Common Table Expression

• After the declaration of table, you can refer the created CTE multiple times in the scope of the same query.
• CTE Can be used in place of views or temporary tables.
• A recursive query is easily created through CTE.

Syntax of CTE

    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.

Example 2:

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.

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.

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

1. 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.
2. Now the query again goes and checks for that person where the ManagerID of the person matches with EmpID in point 1. Here we get 3 employees 1003, 1004, and 1006 which has manager 1005.
3. Also, the EmpLevel counter is increased to 2.
4. 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.

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.

When to use CTE

1. When you need a recursive query where it gets the advantage of being reference itself. A recursive CTE gets executed repeatedly. See an employee example above.
2. It can be used when you want to use View like functionality but do not want to create metadata.
3. When you want to refer the resulting table output multiple times in the same statement.
4. Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.