221
Chapter 9: Merging Data with Joins, Subqueries, and CTEs
9
USE AdventureWorksLT2012;SELECT cst.CompanyName, prod.Name ProductName
FROM SalesLT.Customer cst
INNER JOIN SalesLT.SalesOrderHeader soh
ON cst.CustomerID = soh. CustomerID
INNER JOIN SalesLT.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN SalesLT.Product prod
ON sod.ProductID = prod.ProductID
INNER JOIN SalesLT.ProductCategory pc
ON prod.ProductCategoryID = pc.ProductCategoryID
WHERE pc.Name = 'Cranksets'
ORDER BY cst.CompanyName, prod.Name;Result:
CompanyName ProductName
--------------------------------- -------------------------
Instruments and Parts Company HL Crankset
Instruments and Parts Company LL Crankset
Metropolitan Bicycle Supply HL Crankset
Metropolitan Bicycle Supply LL Crankset
Professional Sales and Service HL Crankset
Trailblazing Sports HL Crankset
Trailblazing Sports LL CranksetThree companies appear multiple times because they purchased two types of Cranksets.Joins are not limited to primary and foreign keys. The join can match a row in one data source with a row in another
data source using any column, as long as the columns share compatible data types and the data matches.Following is a summary of the main points about inner joins:■ They match only rows with a common value.■ (^) The order of the data sources is unimportant.
■ They can appear to multiply rows.
■ (^) Newer ANSI 92 style is the best way to write them.
Outer Joins
Whereas an inner join contains only the intersection of the two data sets, an outer join
extends the inner join by adding the nonmatching data from the left or right data set, as
shown in Figure 9-4.
Outer joins solve a signifi cant problem for many queries by including all the data regardless of a
match. The common customer-order query demonstrates this problem well. If the requirement is
c09.indd 221c09.indd 221 7/30/2012 4:25:06 PM7/30/2012 4:25:06 PM
http://www.it-ebooks.info