Microsoft® SQL Server® 2012 Bible

232

Part II: Building Databases and Working with Data

Set Difference Queries ........................................................................................

A query type that’s useful for analyzing the correlation between two data sets is a set

difference query, sometimes called a left (or right) anti-semi join, which fi nds the difference

between the two data sets based on the conditions of the join. In relational algebra terms,

it removes the divisor from the dividend, leaving the difference. This type of query is the

inverse of an inner join. Informally, it’s called a find unmatched rows query.

Set difference queries are great for locating out-of-place data or data that doesn’t match,

such as rows that are in data set one but not in data set two (see Figure 9-8).

FIGURE 9-8

The set difference query fi nds data outside the intersection of the two data sets.

Old Thing

Table One Table Two

Blue Thing

Plane

Cycle

Train

Red Thing

New Thing

Car

Set

Difference

Set

Difference

Left Set Difference Query

A left set difference query fi nds all the rows on the left side of the join without a match on

the right side of the joins.

Using the Customer2 and SalesOrder2 sample tables, the following query locates all

rows in Customer2 without a match in table SalesOrder2, removing set two (the divi-

sor) from set one (the dividend). The result is the rows from set one that do not have a

match in set two.

The outer join already includes the rows outside the intersection, so to construct a set dif-

ference query, use an OUTER JOIN with an IS NULL restriction on the second data set’s

primary key. This returns all the rows from Customer2 that do not have a match in table

SalesOrder2:

USE tempdb;

SELECT c.CustomerID, c.LastName, so.OrderNumber

c09.indd 232c09.indd 232 7/30/2012 4:25:09 PM7/30/2012 4:25:09 PM

http://www.it-ebooks.info