When to Use the EXCEPT Operator

I was recently asked by a jr level guy when exactly it would be appropriate to use EXCEPT.  I've written about [[INTERSECT and EXCEPT]] when these first made their appearance in SQL Server 2005.  The problem for most people is they are not sure when to use a new feature unless they know the problem the new feature is trying to solve, or the new pattern the feature affords to us.  

Briefly, EXCEPT shows you everything in the first SELECT that does not exist in the second SELECT.  Great, so now when do we use it?

  • If you would normally code a WHERE NOT EXISTS or WHERE NOT IN, you can (usually) replace that with an EXCEPT.  
  • If you would normally code a LEFT OUTER JOIN with a NULL filter in the WHERE clause you can probably replace that with an EXCEPT.  

In essence, we are removing one set of data from another.  

So which is better, WHERE NOT EXISTS or EXCEPT?  

For readability I think EXCEPT is far easier to read.  Since we don't need to correlate the queries I find EXCEPT is less prone to bugs due to carelessness.  I think it's also easier to visualize how to unit tests a query with an EXCEPT vs a query written with WHERE NOT EXISTS.  

For performance it is a toss up and you really need to test.  In my experience EXCEPT is a little bit better if you are nesting additional correlated subqueries in your WHERE NOT EXISTS.  EXCEPT is a much cleaner query plan, and although that doesn't mean it is faster, I think your odds of a bad plan getting cached are lessened.  This is a big problem where I work.