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.
Do'stlaringiz bilan baham: |