Sql server® 2012 t-sql fundamentals

 

CHAPTER 6

 

Set Operators

 

 199

The following query returns distinct locations that are customer locations but not employee 

locations.

SELECT country, region, city FROM Sales.Customers 

EXCEPT 

SELECT country, region, city FROM HR.Employees;

This query returns 66 locations, shown here in abbreviated form.

country         region          city 

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

Argentina       NULL            Buenos Aires 

Austria         NULL            Graz 

Austria         NULL            Salzburg 

Belgium         NULL            Bruxelles 

Belgium         NULL            Charleroi 

... 

USA             WY              Lander 

Venezuela       DF              Caracas 

Venezuela       Lara            Barquisimeto 

Venezuela       Nueva Esparta   I. de Margarita 

Venezuela       Táchira         San Cristóbal 

 

(66 row(s) affected)

You can also use alternatives to the EXCEPT operator. One alternative is an outer join that filters only 

outer rows, which are rows that appear in one side but not the other. Another alternative is to use the 

NOT EXISTS predicate. However, if you want to consider two NULL marks as equal, set operators give 

you this behavior by default with no need for special treatment, whereas the alternatives don’t.