Professional SQL Server™ 2005 Programming

6.4. Derived Tables

Sometimes you get in situations where you need towork with the results of a query, but you need to work with the results of that query in a way that doesn't really lend itself to the kinds of subqueries thatI've discussed up to this point. An example is where, for each row in a giventable, you may have multiple results in the subquery, but you're looking for amore complex action that your IN operator provides. Essentially, what I'm talking about here are situations where you wish youcould use a JOIN operator on your subquery.

It's at times like these that you turn to asomewhat lesser known construct in SQL — a derived table. A derived table (sometimes called in "inline view") is made up of the columns and rows of a result set from a query (heck, they have columns, rows, data types, etc., just like normal tables, so why not use them as such?).

Imagine for a moment that you want to get a listof customers that ordered a particular product — say, a minipump. No problem!Your query might look something like this:

SELECT c.FirstName, c.LastName
  FROM Person.Contact AS c
JOIN Sales.SalesOrderHeader AS soh
  ON c.ContactID = soh.ContactID
JOIN Sales.SalesOrderDetail AS sod
  ON soh.SalesOrderID = sod.SalesOrderID

JOIN Production.Product AS p
  ON sod.ProductID = p.ProductID
WHERE p.Name = 'Minipump'

Okay, so that was easy. Now I'm going to throw you a twist though — now say I want to know all the customers that ordered not only a minipump, but also the AWC Logo Cap. Notice ...