Common Table Expression T-SQL

Common table expressions, a feature introduced by Microsoft SQL Server 2005, are a very powerful feature that may be used in lieu of temporary tables. They may be thought of as a temporary result set that is defined within the execution scope of a query. Using a common table expression you are able to create a reference to itself, which exposes a recursive ability that is very helpful for identifying a hierarchy amongst table elements.

For our example we will use NorthWind’s employee table. This table is designed in such a way that the ReportsTo column is a foreign key field which refers to the primary key field EmployeeID.

 

 WITH Managers AS
(
--Anchor Member
SELECT EmployeeID, FirstName, LastName, ReportsTo 
FROM Employees
WHERE ReportsTo is null
UNION ALL
--Recursive Member
SELECT e.employeeID,e.FirstName, e.LastName, e.ReportsTo
FROM Employees e
INNER JOIN Managers m 
ON e.ReportsTo = m.employeeID
)
SELECT * FROM Managers

Semantics

1. Split the Common Table Expression into anchor and recursive members.

1(a). The anchor is the root level node of the result set. In our example Andrew Fuller has no report to property. Thus, we declare our anchor value with ReportsTo = NULL


SELECT EmployeeID, FirstName, LastName, ReportsTo 
FROM Employees
WHERE ReportsTo is null

1(b). The recursive member must be joined to the anchor using UNION ALL


UNION ALL
--Recursive Member
SELECT e.employeeID,e.FirstName, e.LastName, e.ReportsTo
FROM Employees e
INNER JOIN Managers m 
ON e.ReportsTo = m.employeeID

Any rows returned are inserted into the CTE, which are also then available (seen) on the next recursion. Recursion occurs because of the query referencing the CTE itself based on the Employee in the Managers CTE as input. The recursive query is repeated until it returns an empty result set.

2. The final step is to query the results of the Common Table Expression.


SELECT * FROM Managers

A drawback of this feature is that a Common Table Expression must be called immediately after definition. Any subsequent query referencing the Common Table Expression will fail.

Tags: Common Table Expression, CTE, self joining table, self referential table, SQL, T-SQL

This entry was posted on Friday, October 8th, 2010 at %I:%M %p and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.