INTERSECT and EXCEPT

Last Post 12 Sep 2006 05:51 AM by xfonhe. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
chrisgoddard
New Member
New Member

--
07 Sep 2006 06:26 AM
I've just come across the new INTERSECT and EXCEPT functionality. The question I have is do these constructs provide any performance benefits over alternative implementations and are there any rules of thumb on when to use them.

For example, if I have a table, TableA which contains data which, when processed is put into TableB. In both tables the column is idnumber.

In this example I can find the unprocessed rows by either using:

SELECT idnumber FROM TableA
EXCEPT
SELECT idnumber FROM TableB

or I can use a correlated subquery:

SELECT idnumber from TableA
WHERE NOT EXISTS (select * FROM TableB WHERE TableA.idnumber = TableB.idnumber)

or I can use a LEFT OUTER JOIN:

SELECT idnumber from TableA
LEFT OUTER JOIN TableB
ON TableA.idnumber = TableB.idnumber
WHERE TableB.idnumber

Using my real data where I have 23,000 rows on TableA and 82,000 rows on TableB I get the following timings:

SELECT..EXCEPT 140ms
Correlated Subquery 133ms
LEFT OUTER JOIN 144ms


Prima facie, much of a muchness. Of course this is a very simple query, but I would like to know if anyone has particular knowledge of the usage of EXCEPT and INTERSECT.
xfonhe
New Member
New Member

--
12 Sep 2006 05:51 AM
EXCEPT and INTERSECT return a set of DISTINCT matching values. For your JOIN and correlated subquery to return the same result you need to include DISTINCT or a GROUP BY.

When you view the EXCEPT or INTERSECT in execution plan you will see they are treated as left anti semi join/left semi join, respectively. Only real value I see is less typing (shorter code).
You are not authorized to post a reply.

Acceptable Use Policy