The EXCEPT operation was added to SQL Server in 2005. The operation compares the results of two sets. The results of the first set returns a list for every DISTINCT row that does not have a matching row in the second set.
This is the equivalent of executing a SELECT DISTINCT from some object, joining to a second set using a LEFT OUTER JOIN, and specifying that the outer table record be null.
Both queries perform the same function. When, then, is the EXCEPT operation necessary? Perhaps it makes things easier to understand when working with sets. Frankly I am not sure I see the added benefit except that now there are two different ways to do the same thing. Also, with the EXCEPT operation I don’t have to remember to use DISTINCT or GROUP by to get a unique list from the first set.
I would imagine this query would be more optimized for this combination of requirements as well. Perhaps someone has done some performance test comparisons they would like to share.
Are you using EXCEPT, or still hanging onto the LEFT OUTER JOIN syntax? Share your thoughts here online, or drop an email to btaylor@sswug.org.
Cheers,
Ben