Although CTEs (Common Table Expressions) have been around since SQL 2005 I have found that most people are still not using them. Basically, CTEs can be used in two ways. First they can be used to return a temporary result set when nested inside another SQL statement. Second, they can be used in recursive queries.

Recursive queries can be a little bit difficult to master. However, when used properly they are very powerful. Recursive queries are a often used with hierarchical data. The most classical example or a recursive query is when you are given an organization structure and you need to represent the same structure in a relational database and run queries against it. Here is the syntax of a CTE:

   1:  WITH cte_name (optional column list) AS 

   2:  (   

   3:     Cte_query_1   

   4:     UNION ALL   

   5:     Cte_query_2 ) 

   6:  Statement that uses the above CTE

   7:  OPTION (MAXRECURSION n)

From the syntax above you can see that the CTE consists of 2 queries combined with a UNION ALL. The first query is referred to as the anchor member and it molds the result set. The second query is referred to as the recursive member. The recursive member forms the next part of the result set using the anchor member as input.  OK, enough with the technical jargon, lets do an example using the AdventureWorks sample database:

   1:  USE AdventureWorks;

   2:  GO

   3:   

   4:  WITH DirectReports(LoginID, ManagerID, EmployeeID) AS 

   5:  (    

   6:     SELECT LoginID, ManagerID, EmployeeID    

   7:     FROM HumanResources.Employee    

   8:     WHERE ManagerID IS NULL    

   9:     UNION ALL    

  10:     SELECT e.LoginID, e.ManagerID, e.EmployeeID    

  11:     FROM HumanResources.Employee e    

  12:     INNER JOIN DirectReports d    

  13:     ON e.ManagerID = d.EmployeeID )

  14:   

  15:  SELECT * FROM DirectReports;

  16:  GO

Lets break down the query:

• First we setup the result set with the anchor query. This is the line SELECT LoginID, ManagerID, EmployeeID FROM HumanResources.Employee WHERE ManagerID IS NULL. In plain english, this query selects all employees who are top level managers.
• Then we setup the recursive member to find the employee(s) who work for each top level manager by using the ManagerID key and referencing the DirectReports CTE. This is the line: SELECT e.LoginID, e.ManagerID, e.EmployeeID FROM HumanResources.Employee e INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID
• Finally, we return the result set by issuing the statement SELECT * FROM DirectReports

The result of this query is:

CTEs are very powerful, in the old days you would have to use a combination of cursors and temporary tables to accomplish the same feat. The syntax is fairly easy to understand and best of all, it is ANSI SQL 99 compliant.