Sql server® 2012 t-sql fundamentals

198  

Microsoft SQL Server 2012 T-SQL Fundamentals

The EXCEPT Operator

In set theory, the difference of sets A and B (A – B) is the set of elements that belong to A and do not 

belong to B. You can think of the set difference A – B as A minus the members of B also in A. Figure 6-3 

shows a graphical depiction of the set difference A – B.

A

B

Difference: A – B

FIGuRE 6-3

  Set difference.

In T-SQL, set difference is implemented with the EXCEPT set operator. EXCEPT operates on the re-

sult sets of two input queries and returns rows that appear in the first input but not the second. After 

I describe the EXCEPT (implicit DISTINCT) operator, I’ll describe EXCEPT ALL, which has not yet been 

implemented as of SQL Server 2012, and how to provide an alternative to this operator.