226
Part II: Building Databases and Working with Data
All the order numbers from the SalesOrder2 table are now listed along with every row
from Customer2:CustomerID LastName OrderNumber
--------------- ---------- ----------------
101 Smith NULL
102 Adams 1
103 Reagan NULL
NULL NULL 2
NULL NULL 3As this example shows, full outer joins are an excellent tool for fi nding all the data, even
bad data. Set difference queries, explored later in this chapter, build on outer joins to zero
in on bad data.Placing the Conditions Within Outer Joins
When working with inner joins, a condition has the same effect whether it’s in the JOIN
clause or the WHERE clause, but that’s not the case with outer joins:■ (^) When the condition is in the JOIN clause, SQL Server includes all rows from the
outer table and then uses the condition to include rows from the second table.
■ (^) When the restriction is placed in the WHERE clause, the join is performed, and then
the WHERE clause is applied to the joined rows.
The following two queries demonstrate the effect of the placement of the condition.
In the fi rst query, the left outer join includes all rows from Customer2 and then joins
those rows from SalesOrder2 where CustomerID is equal in both tables and LastName’s
value is Adams. The result is all the rows from Customer2 and rows from SalesOrder2
that meet both join restrictions:
SELECT c.CustomerID, c.LastName , so.OrderNumber
FROM dbo.Customer2 c
LEFT OUTER JOIN dbo.SalesOrder2 so
ON c.CustomerID = so.CustomerID
AND c.LastName = 'Adams';
Result:
CustomerID LastName OrderNumber
101 Smith NULL
102 Adams 1
103 Reagan NULL
The second query fi rst performs the left outer join, producing the same three rows as the
previous query but without the AND condition. The WHERE clause then restricts that result
c09.indd 226c09.indd 226 7/30/2012 4:25:07 PM7/30/2012 4:25:07 PM
http://www.it-ebooks.info