Common Table Expressions (CTE) and Recursive Queries

A CTE is a temporary result set and are similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. A CTE is generally considered to be more readable than a derived table and does not require the extra effort of declaring a Temp Table . CTE is more powerful than a derived table as it can also be self-referencing, or even referenced multiple times in the same query.

CTE defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement

CTE can be referenced multiple ties in the same statement

WITH EmpCTE

AS ( SELECT EmpID, FirstName, LastName, ManagerID, 1 Level

FROM Employee

WHERE ManagerID IS NULL

UNION ALL

SELECT EmpID, FirstName, LastName, ManagerID, Level+1

FROM Employee

INNER JOIN EmpCTE ON Employee.ManagerID = EmpCTE.EmpID

WHERE Employee.ManagerID IS NOT NULL )

SELECT *

FROM EmpCTE