242
Part II: Building Databases and Working with Data
Using Subqueries as Tables
In the same way that you can use a view in the place of a table within the FROM clause of
a SELECT statement, a subquery in the form of a derived table can replace a table, provided
the subquery has an alias. This technique is powerful and is often used to break a diffi cult
query problem down into smaller bite-size chunks.A derived table solution is quite useful when you need to aggregate data before querying
for the result set:SELECT P1.Name,
a.ProductID ,
a.ProductTotal ,
a.ModifiedDate
FROM SalesLT.Product p1
JOIN (SELECT sod.ProductID,
SUM(sod.LineTotal) AS 'ProductTotal',
sod.ModifiedDate
FROM SalesLT.SalesOrderDetail sod
JOIN SalesLT.Product p
ON sod.ProductID = p.ProductID
JOIN SalesLT.ProductCategory pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE pc.Name = 'Vests'
GROUP BY sod.ProductID, sod.ModifiedDate
)a
ON p1.ProductID = a.ProductIDFor more information about aggregate functions and the GROUP BY keyword, see Chapter 10,
“Aggregating, Windowing, and Ranking Data.”All, Some, and Any
Though not as popular as IN, three other options are worth examining when using a sub-
query in a WHERE clause. Each provides a twist on how items in the subquery are matched
with the WHERE clause’s test value. ALL must be true for every value. SOME and ANY,
which are equivalent keywords, must be true for some of the values in the subquery.The next query demonstrates a simple ALL subquery. In this case, select returns true if 1
is less than every value in the subquery:SELECT 'True' as 'AllTest'
WHERE 1 < ALL
(SELECT a
FROM
(VALUES
(2),
(3),c09.indd 242c09.indd 242 7/30/2012 4:25:10 PM7/30/2012 4:25:10 PM
http://www.it-ebooks.info