CTE (Common Table Expression)

CTE stands for Common Table expressions. It was introduced with SQL Server2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.

You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.

SQL Server supports two types of CTEs—recursive and nonrecursive. In this article, I explain how to create both types.

;With CTE1(Address, Name, Age)--Column names for CTE, which are optional

AS

(

SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr

INNER JOIN EMP Emp ON Emp.EID = Addr.EID

)

SELECT * FROM CTE1 --Using CTE

WHERE CTE1.Age > 50

ORDER BY CTE1.NAME

When to use CTE

1.       This is used to store result of a complex sub query for further use.

2.       This is also used to create a recursive query.

Recursive CTE

Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression). 

Here, the result of CTE is repeatedly used to get the final resultset. The following example will explain in detail where I am using AdventureWorks database and try to find hierarchy of Managers and Employees.

USE AdventureWorks
GO
WITH Emp_CTE AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title,e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE
GO

In the above example Emp_CTE is a Common Expression Table, the base record for the CTE is derived by the first sql query before UNION ALL. The result of the query gives you the EmployeeID which don’t have ManagerID.

Second query after UNION ALL is executed repeatedly to get results and it will continue until it returns no rows. For above e.g. Result will have EmployeeIDs which have ManagerID (ie, EmployeeID of the first result).  This is obtained by joining CTE result with Employee table on columns EmployeeID of CTE with ManagerID of table Employee.

This process is recursive and will continue till there is no ManagerID who doesn’t have EmployeeID.