Unlike derived tables, CTEs can reference themselves. You can use a CTE in place of a view when you don't need to store the view. You can also reference the CTE multiple times within a single statement. Using a CTE, you can group your results by a derived column.

We've written in other tips and in our MySQL book about atomic and molecular queries: atomic queries address one table, while molecular queries are built up from the atoms. The approach improves clarity and reusability. Using CTEs, you can do the same thing: isolate parts of the query into readable "chunks", then build up a complex query from the chunks.

The most significant and powerful thing you can do with a CTE is perform a recursive query.

Creating a CTE

You create a CTE using the keyword WITH. The template is:

WITH CTE_name[ (column_name [,...n] ) ]
AS
( CTE_query_specification )

If the column names mentioned in the CTE definition are unique, you don't need to name them. However, naming them also lets you give them new column names. Here is an example that uses the AdventureWorks sample database included with SQL Server 2005. This database is highly normalized and, as a result, requires several joins to assemble the information concerning employees. Views simplify this, but also gather all the information regarding employees, and you may only require some of it.

Employee data in AdventureWorks is distributed among several tables. This is further complicated by the fact that employees and managers are both stored in a single table (HumanResources.Employee), while their names (and other data) are stored in the Person.Contact table.

First, let's create a CTE that retrieves the employee name:

WITH cte_Employee
AS
(
      SELECT  e.EmployeeID, c.FirstName, c.MiddleName, c.LastName,
e.Title AS JobTitle, c.Phone, e.ManagerID
      FROM    HumanResources.Employee AS e
INNER JOIN Person.Contact AS c ON c.ContactID = e.ContactID
)

Then, we can select one or more columns from the CTE as if it were a standard table or view.

Now up the ante a little. We want the names of the employees and their managers, so we'll use the CTE twice, joining it to itself. Here is the complete query:

WITH cte_Employee AS (
      SELECT  e.EmployeeID, c.FirstName, c.MiddleName, c.LastName,
              e.Title AS JobTitle, c.Phone, e.ManagerID
      FROM    HumanResources.Employee AS e
      INNER JOIN Person.Contact AS c ON c.ContactID = e.ContactID
)
SELECT E.FirstName + ' ' E.LastName Employee,
       M.FirstName + ' ' M.LastName Manager
FROM   cte_Employee AS E
       LEFT OUTER JOIN cte_Employee AS M
       ON E.ManagerID = M.EmployeeID

Summary

CTEs add a powerful and slick new feature to SQL Server 2005 syntax. They improve query readability and help encapsulate query modules or atoms.

Last updated 15 Jan 2025