Sql server® 2012 t-sql fundamentals

The 

EXCEPT ALL

 Multiset Operator

I provide this section as optional reading for those who feel very comfortable with the material cov-

ered so far in this chapter. The EXCEPT ALL operator is very similar to the EXCEPT operator, but it also 

takes into account the number of occurrences of each row. Provided that a row R appears x times in 

the first multiset and y times in the second, and x > y, R will appear x - y times in Query1 EXCEPT ALL 

Query2. In other words, at the logical level, EXCEPT ALL returns only occurrences of a row from the 

first multiset that do not have a corresponding occurrence in the second.

SQL Server does not provide a built-in EXCEPT ALL operator, but you can provide an alternative 

with a very similar solution to the one provided for INTERSECT ALL. Namely, add a ROW_NUMBER 

calculation to each of the input queries to number the occurrences of each row, and use the EXCEPT 

operator between the two input queries. Only occurrences that don’t find matches will be returned.

The following example shows how you can use EXCEPT ALL to return occurrences of employee 

locations that have no corresponding occurrences of customer locations.

www.it-ebooks.info

---

200  

Microsoft SQL Server 2012 T-SQL Fundamentals

WITH EXCEPT_ALL 

AS 

( 

  SELECT 

    ROW_NUMBER()  

      OVER(PARTITION BY country, region, city 

           ORDER     BY (SELECT 0)) AS rownum, 

    country, region, city 

    FROM HR.Employees 

 

  EXCEPT 

 

  SELECT 

    ROW_NUMBER()  

      OVER(PARTITION BY country, region, city 

           ORDER     BY (SELECT 0)), 

    country, region, city 

  FROM Sales.Customers 

) 

SELECT country, region, city 

FROM EXCEPT_ALL;

This query returns the following output.

country         region          city 

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

USA             WA              Redmond 

USA             WA              Tacoma 

USA             WA              Seattle

Precedence

SQL defines precedence among set operators. The INTERSECT operator precedes UNION and EXCEPT, 

and UNION and EXCEPT are considered equal. In a query that contains multiple set operators, first 

INTERSECT operators are evaluated, and then operators with the same precedence are evaluated 

based on order of appearance.

Consider the following query, which shows how INTERSECT precedes EXCEPT.

SELECT country, region, city FROM Production.Suppliers 

EXCEPT 

SELECT country, region, city FROM HR.Employees 

INTERSECT 

SELECT country, region, city FROM Sales.Customers;

Because INTERSECT precedes EXCEPT, the INTERSECT operator is evaluated first, even though it 

appears second. Therefore, the meaning of this query is, “locations that are supplier locations but not 

(locations that are both employee and customer locations).”

www.it-ebooks.info

---