241
Chapter 9: Merging Data with Joins, Subqueries, and CTEs
9
Road Bikes 185513.0436
Mountain Frames 54949.602
Jerseys 7094.1662
Pedals 2996.496
Road Frames 24346.584...
Be cautious of overusing scalar subqueries. Because they must execute for each row
returned by the main query, a large result set that includes a scalar subquery can result in
poor performance.Using Subqueries as Lists
One common use of subqueries is to provide a list to an IN operator. In this scenario, a
single column is returned from the subquery with one or more values contained in it. The
IN operator returns a value of true if the column value is found anywhere in the list sup-
plied by the subquery.A list subquery serves as a dynamic means to generate the WHERE ... IN condition list:SELECT FirstName, LastName
FROM dbo.Contact
WHERE Region IN (Subquery that returns a list of states);Nested Subqueries
Subqueries can be nested, and just as the simple subquery can execute before the outer
main query, nested subqueries execute from the inside out. The most inner subquery
executes fi rst, passing its results up to the next most inner subquery and then executes it.
This pattern repeats until the fi nal outer query executes.SELECT Name as ProductName
FROM SalesLT.Product
WHERE ProductID IN
(SELECT ProductID
FROM SalesLT.SalesOrderDetail
WHERE SalesOrderID IN
(SELECT SalesOrderID -- Find the Orders with vests
FROM SalesLT.SalesOrderDetail
WHERE ProductID IN
(SELECT ProductID
FROM SalesLT.Product
WHERE ProductCategoryID =
-- 1. Find the Vests category
(Select ProductCategoryID
FROM SalesLT.ProductCategory
Where Name = 'Vests' ) ) ) );c09.indd 241c09.indd 241 7/30/2012 4:25:10 PM7/30/2012 4:25:10 PM
http://www.it-ebooks.info