240
Part II: Building Databases and Working with Data
SELECT...
FROM CTE1Name
INNER JOIN CTE2Name
ON...Although CTEs may include complex queries, they come with two key restrictions:■ (^) CTEs may not be nested. A CTE may not include another CTE.
■ (^) The CTE defi nition may not refer to the referencing query (the one that comes
immediately after the CTE). However, a CTE may reference any CTE defi ned before it
if multiple CTEs are defi ned and comma-separated.
A CTE is actually just a different syntax for a simple subquery used as a derived table, with one key
exception: CTEs can recursively refer to the same table during runtime using a UNION ALL. For more
details on using CTEs for hierarchies, turn to Chapter 13, “Working with Hierarchies.”
Using Scalar Subqueries
If the subquery returns a single value, it may then be used anywhere inside the SELECT
statement where an expression might be used, including column expressions, JOIN condi-
tions, WHERE conditions, or HAVING conditions.
Normal operators (+, =, between, and so on) work with single values returned from a sub-
query; data-type conversion using the CAST() or CONVERT() functions may be required,
however.
The example in the last section used a subquery within a WHERE condition. The following
sample query uses a subquery within a column expression to calculate the total sales:
SELECT pc.Name as ProductCategoryName ,
SUM(OrderQty UnitPrice) AS Sales
FROM SalesLT.SalesOrderDetail AS sod
INNER JOIN SalesLT.Product AS pd
ON sod.ProductID = pd.ProductID
INNER JOIN SalesLT.ProductCategory AS pc
ON pd.ProductCategoryID = pc.ProductCategoryID
GROUP BY pc.Name
ORDER BY Count() DESC;
The subquery, SELECT SUM(OrderQty * UnitPrice) from SalesLT
.SalesOrderDetail, returns one value: 714002.9136. This number represents all sales
in the entire table. The abbreviated result lists the product categories and sales amount:
ProductCategoryName Sales
Touring Bikes 221081.9622
Mountain Bikes 173085.846
c09.indd 240c09.indd 240 7/30/2012 4:25:10 PM7/30/2012 4:25:10 PM
http://www.it-ebooks.info