Sql server® 2012 t-sql fundamentals

The 

INTERSECT ALL

 Multiset Operator

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

covered so far in this chapter. Standard SQL supports an ALL flavor of the INTERSECT operator, 

but this flavor has not yet been implemented as of SQL Server 2012. After I describe the meaning 

of INTERSECT ALL in standard SQL, I’ll provide an alternative in T-SQL.

www.it-ebooks.info

---

196  

Microsoft SQL Server 2012 T-SQL Fundamentals

Remember the meaning of the ALL keyword in the UNION ALL operator: it returns all duplicate 

rows. Similarly, the keyword ALL in the INTERSECT ALL operator means that duplicate intersections 

will not be removed. INTERSECT ALL is different from UNION ALL in that the former does not return 

all duplicates but only returns the number of duplicate rows, matching the lower of the counts in both 

multisets. Another way to look at it is that the INTERSECT ALL operator doesn’t only care about the 

existence of a row in both sides—it also cares about the number of occurrences of the row in each 

side. It’s as if this operator looks for matches per occurrence of each row. If there are x occurrences 

of a row R in the first input multiset and y occurrences of R in the second, R appears minimum(x, y) 

times in the result of the operator. For example, the location (UK, NULL, London) appears four times 

in Employees and six times in Customers; hence, an INTERSECT ALL operator between the employee 

locations and the customer locations should return four occurrences of (UK, NULL, London), because 

at the logical level, four occurrences can be intersected.

Even though SQL Server does not support a built-in INTERSECT ALL operator, you can provide a 

solution that produces the same result. You can use the ROW_NUMBER function to number the oc-

currences of each row in each input query. To achieve this, specify all participating attributes in the