233
Chapter 9: Merging Data with Joins, Subqueries, and CTEs
9
FROM dbo.Customer2 c
LEFT OUTER JOIN dbo.SalesOrder2 so ON c.CustomerID = so.Customer
ID WHERE so.OrderNumber IS NULL;Customer2’s difference follows:CustomerID LastName OrderNumber
----------- ---------------- ----------------
101 Smith NULL
103 Reagan NULLFull Set Difference Queries
You can use a modifi ed version of this technique to clean up bad data during conversions. A
full set difference query is the logical opposite of an inner join. It identifi es all rows outside
the intersection from either data set by combining a full outer join with a WHERE restric-
tion that accepts only nulls in either primary key:SELECT c2.CustomerID c2CustomerID, so2.CustomerID so2CustomerID
FROM dbo.Customer2 c2
FULL OUTER JOIN dbo.SalesOrder2 so2
ON c2.CustomerID = so2.CustomerID
WHERE c2.CustomerID IS NULL
OR so2.CustomerID IS NULL;The result is every row without a match in the sample tables:c2CustomerID so2CustomerID
------------ -------------
101 NULL
103 NULL
NULL 104
NULL 105Using the result, the bad data can either be remediated or removed.Using Unions .....................................................................................................
The UNION operation is different from a join. In relational algebra terms, a union is
addition, whereas a join is multiplication. Instead of extending a row horizontally as a
join would, the union stacks multiple result sets into a single long table, as shown in
Figure 9-9.c09.indd 233c09.indd 233 7/30/2012 4:25:09 PM7/30/2012 4:25:09 PM
http://www.it-ebooks.info