245
Chapter 9: Merging Data with Joins, Subqueries, and CTEs
9
The query asks, “Which products have a list price that is less than the average sale price
for all items in a product category?” The query uses a correlated subquery to determine
the average sales price of items in a product category. The subquery is executed for
every row in the Product table, using the outer query’s named range, p, to reference
the outer query. If a ProductCategoryID match exists for that row, the ListPrice
for the ProductID is compared to the calculated average price. If the ListPrice is
less than the average price sold, the condition is true and the row is accepted into the
result set:SELECT p.ProductID , p.ProductCategoryID , p.Name
FROM SalesLT.Product p
WHERE ListPrice <
(SELECT
SUM(OrderQty * UnitPrice) /Sum(sod.OrderQty)
AS AveragePricePerItemInCategory
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
WHERE pc.ProductCategoryID = p.ProductCategoryID
GROUP BY pc.Name);Result:ProductID ProductCategoryID ProductName ListPrice
---------- ----------------- ----------------------------- ---------
722 18 LL Road Frame - Black, 58 337.22
723 18 LL Road Frame - Black, 60 337.22
724 18 LL Road Frame - Black, 62 337.22
725 18 LL Road Frame - Red, 44 337.22
726 18 LL Road Frame - Red, 48 337.22
727 18 LL Road Frame - Red, 52 337.22
728 18 LL Road Frame - Red, 58 337.22
729 18 LL Road Frame - Red, 60 337.22
730 18 LL Road Frame - Red, 62 337.22
736 18 LL Road Frame - Black, 44 337.22
737 18 LL Road Frame - Black, 48 337.22
738 18 LL Road Frame - Black, 52 337.22
759 6 Road-650 Red, 58 782.99
760 6 Road-650 Red, 60 782.99Correlating a Derived Table Using Apply
In a WHERE clause, a subquery can be correlated, referring up to the outer query. However,
a derived table subquery in the FROM clause is not allowed to reference the outer query if
it is part of a JOIN. However, the CROSS APPLY or OUTER APPLY method of including a
subquery in the FROM clause enables you to pass data from the outer query into the derived
table subquery.c09.indd 245c09.indd 245 7/30/2012 4:25:11 PM7/30/2012 4:25:11 PM
http://www.it-ebooks.info