Sql server® 2012 t-sql fundamentals

 

CHAPTER 6

 

Set Operators

 

 197

This code produces the following output.

rownum               country         region          city 

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

1                    UK              NULL            London 

1                    USA             WA              Kirkland 

1                    USA             WA              Seattle 

2                    UK              NULL            London 

3                    UK              NULL            London 

4                    UK              NULL            London

Of course, the INTERSECT ALL operator is not supposed to return any row numbers; those are 

used to support the solution. If you don’t want to return those in the output, you can define a table 

expression such as a common table expression (CTE) based on this query and select only the original 

attributes from the table expression. Here’s an example of how you can use INTERSECT ALL to return 

all occurrences of employee and customer locations that intersect.

WITH INTERSECT_ALL 

AS 

( 

  SELECT 

    ROW_NUMBER()  

      OVER(PARTITION BY country, region, city 

           ORDER     BY (SELECT 0)) AS rownum, 

    country, region, city 

  FROM HR.Employees 

 

  INTERSECT 

 

  SELECT 

    ROW_NUMBER()  

      OVER(PARTITION BY country, region, city 

           ORDER     BY (SELECT 0)), 

    country, region, city 

  FROM Sales.Customers 

) 

SELECT country, region, city 

FROM INTERSECT_ALL;

Here’s the output of this query, which is equivalent to what the standard INTERSECT ALL would 

have returned.

country         region          city 

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

UK              NULL            London 

USA             WA              Kirkland 

USA             WA              Seattle 

UK              NULL            London 

UK              NULL            London 

UK              NULL            London

www.it-ebooks.info

---