Microsoft® SQL Server® 2012 Bible

220

Part II: Building Databases and Working with Data

CustomerID LastName CustomerID OrderNumber

----------- -------- ----------- -----------

103 Reagan 103 5

104 Franklin

105 Dowdry 105 6

105 7

Joins can appear to multiply rows. If a row on one side of the join matches with several

rows on the other side of the join, the result includes a row for every match. In the preced-

ing query, some customers (Smith, Adams, Reagan, and Dowdry) are listed multiple times

because they have multiple orders.

Joins also eliminate rows. Franklin (104) has no matching orders and therefore is elimi-

nated in the join set.

Multiple Data Source Joins

As some of the examples have already demonstrated, a SELECT statement isn’t limited to

one or two data sources (tables, views, CTEs, subqueries, and so on); a SQL Server SELECT

statement may refer to up to 256 data sources. That’s a lot of joins.

Because SQL is a declarative language, the order of the data sources is not important for

inner joins. (The query optimizer decides the best order to actually process the query based

on the indexes available and the data in the tables.) Multiple joins may be combined in

multiple paths, or even circular patterns (A joins B joins C joins A) — a large white board

and a consistent development style can pay off.

The following query (fi rst shown in Figure 9-5 and then worked out in code) answers the

question, “Who purchased a crankset from AdventureWorks?” The answer must involve fi ve

tables from AdventureWorks2012LT:

■ (^) The ProductCategory table for fi nding all ProductCategoryIDs that are classifi ed
as Cranksets.
■ (^) The Product table for the set of specifi c ProductIDs that are touring bikes. Their
rows match on ProductCategoryID to the previous set.
■ (^) The SalesOrderDetail table for the set of SalesOrderIDs that contained one of
the touring bike products (one of the ProductIDs found in the preceding set).
■ (^) The SalesOrderHeader table for the set of CustomerIDs that contain the preced-
ing set of SalesOrderIDs.
■ (^) The Customer table for the set of Customers (CompanyName) that matches the set
of CustomerIDs found in SalesOrderHeader.
The following SQL SELECT statement begins with the “who” portion of the question and
specifi es the join tables and conditions as it works through the required tables. The
WHERE clause restricts the ProductCategory table rows and yet affects the customers
selected:
c09.indd 220c09.indd 220 7/30/2012 4:25:06 PM7/30/2012 4:25:06 PM
http://www.it-ebooks.info