Microsoft SQL Server 2012 Bible

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 finds 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 finds data outside the intersection of the two data sets.

Left Set Difference Query

A left set difference query finds 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 divisor) 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 difference 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 ...