In a previous post, I discussed how to use common table expressions to generate temporary result sets that can read from and written to. In this post, I’ll show how to use two CTEs together for more complex scenarios.
To illustrate how to use CTEs this way, we’ll code up a simple example using the AdventureWorks sample database. In this example, we first create a simple CTE with a list of all employee IDs and their respective contact ID. We can take a look at the results by selecting all rows out of the CTE:
;WITH EmployeeCTE AS ( SELECT EmployeeID, ContactID FROM HumanResources.Employee ) SELECT * FROM EmployeeCTE
This example is pretty simple to understand. When we execute the above SQL query, we get results similar to the following:
Now what if we wanted to link the Employee record to the Contact record? We could do this in a very simple query by using a JOIN, but we want to use multiple CTEs right? So, we’ll simply put a comma after our first CTE and then declare a new one right afterwards. When declaring this new CTE, we can query the previous CTE in the sequence. So, that’s exactly what we will do. We’ll do a JOIN on the Person.Contact table via the ContactID field so that we can match an EmployeeID with a person’s first name and last name.
;WITH EmployeeCTE AS ( SELECT EmployeeID, ContactID FROM HumanResources.Employee ), EmployeeContactCTE AS ( SELECT EmployeeID, Person.Contact.FirstName, Person.Contact.LastName FROM EmployeeCTE INNER JOIN Person.Contact ON Person.Contact.ContactID = EmployeeCTE.ContactID ) SELECT * FROM EmployeeContactCTE
When we execute the above statement, the results will look similar to the following:
That’s really all there is to using multiple CTEs in sequence. You can chain more CTEs together by simply separating them with commas as illustrated above. I’m not aware of a limit of how many CTEs you can use in sequence, but if you do, feel free to leave a comment!