don't forget that except implicitly compares every column, not just keys. so it can be used in situations where a not exists or not in or outer join wouldn't be appropriate or practical. (would you really want to code a join between tables with 120 columns?)

jcrawf02 (7/30/2008)So, for someone who is using 2000 still, but looking forward to 2005, EXCEPT will let me intersect the two sets, and give me the results that are not overlapping, correct?

Well technically, no. What you describe is the Exclusive Union, or Disjoint Union (which is the Set equivalent of XOR in logic) which in SQL Server could be expressed as:

(A UNION B) EXCEPT (A INTERSECT B)

EXCEPT (which used to be called "MINUS" in some old implementations of SQL) is more like a subtraction operator. It returns only the (distinct) elements of the first set that are not in the second set. In logical operators, EXCEPT would be:

A AND (NOT B)

Of course that may be what you actually meant, but the details really matter when it comes to logic and set theory descriptions.

That is what I actually meant, thanks, and I don't know that I really understand what I meant now, so until I do some more reading, I don't think I'll be using it. ;) Thanks for the info!

By the way, it's very big of you to contribute to the same thread as Jeff, when he's obviously anti-RBARry, but just can't spell your name . . .

One way is already demonstrated in the code I posted previously in this thread. Another way is to set up Profiler for the SPID I'm using to test through and have each code snippet separated from the other using GO.

Of course, you can't test something for performance unless you have lot's of data, so I use a "standard" test table for certain things. The code to generate the "standard" test table can be easily modified to suit a wide variety of requirements. The code generator is based on the same principle as the code I use to very quickly generate a Tally table. Here it is...

I'd like to add something here... I think a few people have touched on it, but it's pretty important.

If you are pulling out a very small amount of records, say, just one or two, then using Except would probably be the better way to go, if you can filter to this level in the except part of your clause.

If you are, however, pulling out a rather large set of data, the left outer joins will be the better choice.

At least, that's what I generally find with nested queries rather than joined queries.

If you can filter down to only a few records early in the game, then nesting is good. Otherwise, it makes your DB take too much of a time hit to query all the records twice, rather than things like hash matches.