MS SQL Server Common Table Expressions

MS SQL Server 2005 introduced Common Table Expression (CTE) which is temporary result set with a name that will be used in SELECT query statement by FROM clause. It makes the queries simpler and better for future maintenance. CTE defines a virtual view that will be used in another data manipulation language (DML) statement, for example in a SELECT.

CTE main elements:
Name of CTE, It comes after WITH keyword.
List of columns, it's optional.
Query that defines the CTE temporary result set,
The quesry sits after AS keyword and inside open and close paranteses.

It's not possible to use COMPUTE, COMPUTE BY, ORDER BY (unless TOP
is used) in a CTE.

For example, in AdventureWorksLT database sample (is available
in CodePlex web pages, you can download it from following here. Product table keeps information about products and category of the products are available in ProductCategory table. The following example provides a temporary result set in the name of CheapProducts which is derived from Product table and those products' price is less than $100 (Ooops, I know, $100 is not cheap thing, just take it easy). Then query that comes after, request for BLACK products with their names and their category.

WITH CheapProducts (ProductName, ProductColor, ProductPrice, ProductCategoryID)
AS
(
   SELECT [name], [color], [listprice], [ProductCategoryID]
   FROM [AdventureWorksLT].[SalesLT].[Product]
   WHERE [listprice] < 100
)
SELECT chpPrd.ProductName, chpPrd.ProductPrice, prdCtg.Name as ProductCategory
   FROM CheapProducts as chpPrd
   JOIN [AdventureWorksLT].[SalesLT].[ProductCategory] as prdCtg
      ON chpPrd.ProductCategoryID = prdCtg.ProductCategoryID
   WHERE chpPrd.ProductColor = 'BLACK'

This one was a simple sample, but when we have a little more
complex queries CTE can really be a good hand to make it simple.
Next example lists the QUANTITY of PRODUCTS (and the products' CATEGORY) which are RED and their price is less than $100, ordered by companies:

WITH PrdAndCat (PrdID, PrdName, PrdColor, PrdPrice, PrdCategory)
AS
(
SELECT prd.[ProductID], prd.[name], prd.[color], prd.[listprice], prdCtg.[Name]
FROM [AdventureWorksLT].[SalesLT].[Product] as prd
JOIN [AdventureWorksLT].[SalesLT].[ProductCategory] as prdCtg
   ON prd.ProductCategoryID = prdCtg.ProductCategoryID
)
SELECT cstmr.CompanyName, ordrHdr.OrderDate, ordrDtl.OrderQty,
pNc.PrdName, pNc.PrdCategory
FROM [AdventureWorksLT].[SalesLT].[SalesOrderHeader] as ordrHdr
JOIN [AdventureWorksLT].[SalesLT].[SalesOrderDetail] as ordrDtl
   ON ordrHdr.SalesOrderID = ordrDtl.SalesOrderID
JOIN [AdventureWorksLT].[SalesLT].[Customer] as cstmr
   ON ordrHdr.CustomerID = cstmr.CustomerID
JOIN PrdAndCat as pNc
   ON pNc.[PrdID] = ordrDtl.[ProductID]
WHERE pNc.PrdColor = 'RED' AND pNc.PrdPrice

Bulk Discount Store              2004-06-01  3      Sport-100 Helmet, Red Helmets
Metropolitan Bicycle Supply      2004-06-01  1      Sport-100 Helmet, Red Helmets
Many Bikes Store                 2004-06-01  2      Sport-100 Helmet, Red Helmets
Riding Cycles                    2004-06-01  6      Sport-100 Helmet, Red Helmets
Action Bicycle Specialists       2004-06-01 10      Sport-100 Helmet, Red Helmets
Eastside Department Store        2004-06-01 10      Sport-100 Helmet, Red Helmets
Professional Sales and Service   2004-06-01  3      Sport-100 Helmet, Red Helmets

MS SQL Server Common Table Expressions