Common table expressions (CTEs) are a powerful feature that was added to Microsoft SQL Server 2005. Common table expressions can be thought of as a temporary result set that can be used with one single SELECT, INSERT, UPDATE, or DELETE statement.
The basic syntax for a CTE is the following:
WITH <cte_name> (column1, column2, column3, …) AS ( <query that builds CTE> ) <query to execute against CTE>
Using the above CTE syntax, we can take a look at the basic CTE below (note that I am using the AdventureWorks sample database for all examples here):
WITH ProductCTE (ProductName, ProductModelName) AS ( SELECT P.Name, PM.Name FROM Production.Product P LEFT OUTER JOIN Production.ProductModel PM ON P.ProductModelID = PM.ProductModelID ) SELECT * FROM ProductCTE WHERE ProductModelName IS NOT NULL ORDER BY ProductName
In the preceding code, we are creating a CTE called ProductCTE which will contains the Name column of the Production.Product table joined with the Name column of the Production.ProductModel table. Then, from this CTE we are selecting all records where the ProductModelName is not equal to NULL. If you execute the above query, you should see results similar to the following:
In the above example, we explicitly named each of the columns in the CTE. This is because both source columns were called Name. Had these been different, the CTE would automatically assign names to the columns. So, if we pull out the CatalogDescription column from the Production.ProductModel table instead of the Name column, we can omit the column names from the CTE definition:
WITH ProductCTE AS ( SELECT P.Name, PM.CatalogDescription FROM Production.Product P LEFT OUTER JOIN Production.ProductModel PM ON P.ProductModelID = PM.ProductModelID ) SELECT * FROM ProductCTE WHERE CatalogDescription IS NOT NULL ORDER BY Name
If you run the CTE above, your results should be similar to the following:
You can also execute an UPDATE query against a CTE. The wonderful part about doing so is that the CTE will map the update back to the source tables automatically. For example, if we run the following CTE query:
WITH ProductCTE AS ( SELECT P.Name, PM.CatalogDescription FROM Production.Product P LEFT OUTER JOIN Production.ProductModel PM ON P.ProductModelID = PM.ProductModelID ) UPDATE ProductCTE SET Name = Name + ' – UPDATED'
This will append “ – UPDATED” to the Name column of the CTE. Since the Name column maps back to the Name column of the ProductModel table, the ProductModel table should have been updated when we ran the previous query. To check, we can run a simple SELECT query:
SELECT P.Name, PM.CatalogDescription FROM Production.Product P LEFT OUTER JOIN Production.ProductModel PM ON P.ProductModelID = PM.ProductModelID
As you can see, the results clearly show that by using the CTE, we have updated the source data:
As you can clearly see from these few simple examples, common table expressions are a powerful tool to have under your belt. This article is just scratching the surface too. Some of the advanced features of CTEs include being able to write recursive queries as well as the ability to combine multiple CTEs in sequence. Stay tuned for articles about these advanced topics in the future.