239
Chapter 9: Merging Data with Joins, Subqueries, and CTEs
9
Common Table Expressions
The common table expression (CTE) defi nes what could be considered a temporary view,
which can be referenced just like a view in the same query. Because CTEs may be used
in the same ways that simple subqueries are used and they compile exactly like a simple
subquery, they are included in the simple subquery heading and show example code CTEs
alongside simple subqueries.The CTE uses the WITH clause, which defi nes the CTE. Inside the WITH clause is the name,
column aliases, and SQL code for the CTE subquery. The main query can then reference the
CTE as a data source:WITH CTEName (Column aliases)
AS (Simple Subquery)
SELECT...
FROM CTEName;The following example is the exact same query as the preceding subquery, only in CTE for-
mat. The name of the CTE is CTEQuery. It returns the ProductCategoryID column and
uses the exact SELECT statement as the preceding simple subquery:WITH CTEQuery (ProductCategoryID)
AS (Select ProductCategoryID
from SalesLT.ProductCategory
Where Name = 'Cranksets')(A CTE by itself is an incomplete SQL statement. If you try to run the preceding code, a syn-
tax error occurs.)After the CTE has been defi ned in the WITH clause, the main portion of the query can refer-
ence the CTE using its name as if the CTE were any other table source, such as a table or a
view. Following is the complete example, including the CTE and the main query:WITH CTEQuery (ProductCategoryID)
AS (Select ProductCategoryID
from SalesLT.ProductCategory
Where Name = 'Cranksets')
SELECT Name as ProductName
FROM SalesLT.Product p
INNER JOIN CTEQuery c
ON p.ProductCategoryID = c.ProductCategoryID;To include multiple CTEs within the same query, defi ne the CTEs in sequence prior to the
main query separated by a comma:WITH
CTE1Name (column names)
AS (Simple Subquery),
CTE2Name (column names)
AS (Simple Subquery)c09.indd 239c09.indd 239 7/30/2012 4:25:10 PM7/30/2012 4:25:10 PM
http://www.it-ebooks.info