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:

[code language=”sql”]
WITH (column1, column2, column3, …) AS
(

)

[/code]

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):

[code language=”sql”]
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
[/code]

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:

Basic CTE

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:

[code language=”sql”]
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
[/code]

If you run the CTE above, your results should be similar to the following:

Basic CTE With No Column Names Specified

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:

[code language=”sql”]
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’
[/code]

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:

[code language=”sql”]
SELECT P.Name, PM.CatalogDescription
FROM Production.Product P
LEFT OUTER JOIN Production.ProductModel PM ON P.ProductModelID = PM.ProductModelID
[/code]

As you can see, the results clearly show that by using the CTE, we have updated the source data:

Result After Update Via CTE

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.

Intro to Common Table Expressions

One thought on “Intro to Common Table Expressions

Leave a Reply