Sql server® 2012 t-sql fundamentals

The INTERSECT Operator

In set theory, the intersection of two sets (call them A and B) is the set of all elements that belong to A 

and also belong to B. Figure 6-2 shows a graphical depiction of the intersection of two sets.

A

B

Intersection: A    B

U

FIGuRE 6-2

  The intersection of two sets.

In T-SQL, the INTERSECT set operator returns the intersection of the result sets of two input que-

ries, returning only rows that appear in both inputs. After I describe INTERSECT (implicit DISTINCT), I 

provide an alternative solution to the INTERSECT ALL multiset operator that has not yet been imple-

mented as of SQL Server 2012.

www.it-ebooks.info

---

 

CHAPTER 6

 

Set Operators

 

 195

The 

INTERSECT

 distinct Set Operator

The INTERSECT set operator logically first eliminates duplicate rows from the two input multisets—

turning them to sets—and then returns only rows that appear in both sets. In other words, a row is 

returned provided that it appears at least once in both input multisets. 

For example, the following code returns distinct locations that are both employee locations and 

customer locations.

SELECT country, region, city FROM HR.Employees 

INTERSECT 

SELECT country, region, city FROM Sales.Customers;

This query returns the following output.

country         region          city 

--------------- --------------- --------------- 

UK              NULL            London 

USA             WA              Kirkland 

USA             WA              Seattle

It doesn’t matter how many occurrences there are of an employee or customer location—if the 

location appears at least once in the Employees table and also at least once in the Customers table, 

the location is returned. The output of this query shows that three locations are both customer and 

employee locations.

I mentioned earlier that when it is comparing rows, a set operator considers two NULL marks as 

equal. There are both customers and employees with the location (UK, NULL, London), but it’s not 

trivial that this row appears in the output. The country and city attributes do not allow NULL marks, 

so the comparison that the set operator performs between these column values in an employee row 

and in a customer row is straightforward. What’s not straightforward is that when the set operator 

compares the NULL region in the employee row and the NULL region in the customer row, it consid-

ers the two equal, and that’s why it returns the row. 

When this is the behavior of NULL comparison that you want—as it is in this case—set operators 

have a powerful advantage over alternatives. For example, one alternative to using the INTERSECT 

operator is to use an inner join, and another is to use the EXISTS predicate. In both cases, when the 

NULL in the region attribute of an employee is compared with the NULL in the region attribute of a 

customer, the comparison yields UNKNOWN, and such a row is filtered out. This means that unless 

you add extra logic that handles NULL marks in a special manner, neither the inner join nor the EXISTS 

alternative returns the row (UK, NULL, London), even though it does appear in both sides.