Jeff Moden (7/19/2009)
Why would anyone think that INTERSECT is easier than INNER JOIN?
In some cases intersect is easier to use than inner join, if you want to compare resultsets. For example, an inner join requires you to add EVERY column to the inner join list or to the predicate to achieve what you could accomplish by use the intersect operator. I am not saying this is the best method, but it can sometimes reduce coding and save you time. Look at the example below and imagine if you wanted to compare tables with 15+ columns. Essentially with intersect, you can copy and paste the same query on both sides of the operator.
Note: To those reading this, you should not use select * in your select list. I did so below to demonstrate a point.
DECLARE @t1 TABLE(
INSERT INTO @t1 VALUES (1,'a',10,'z','z','z');
INSERT INTO @t1 VALUES (2,'b',20,'z','z','z');
INSERT INTO @t1 VALUES (3,'c',30,'z','z','z');
INSERT INTO @t1 VALUES (4,'d',40,'z','z','z');
DECLARE @t2 TABLE(
INSERT INTO @t2 VALUES (1,'a',10,'z','z','z');
INSERT INTO @t2 VALUES (2,'b',20,'z','z','z');
INSERT INTO @t2 VALUES (4,'d',30,'z','z','z');
INSERT INTO @t2 VALUES (5,'e',40,'z','z','z');
FROM @t1 t1
INNER JOIN @t2 t2
ON t1.id = t2.id
AND t1.col1 = t2.col
AND t1.col2 = t2.col2
AND t1.col3 = t2.col3
AND t1.col4 = t2.col4
AND t1.col5 = t2.col5
As for except, I find this operator quite useful when trying to diagnose RI violaters and resultsets that dont match between source and destination. Except has the same benefits as Intersect, which is namely reduced typing.
I personally dont allow or use these operators in production code, but I sometimes use these operators to quickly retrieve information.
I recently blogged about intersect and except. You can have a look if you like: http://jahaines.blogspot.com/2009/07/should-i-intersect-or-except.html
My blog: http://jahaines.blogspot.com