(OK I fully understand how lame the title of this post is but hey it’s the best I could do so live with it)
I am guilty of favoring particular syntax over other options. This is mainly due to habit so every now and again I like to take some time and look at other ways to skin the cat. (PLEASE NOTE: No cats were actually skinned in the making of this post… that being said I hate cats and have pushed many cats off my lap at friends houses cause well it’s my lap and if I wanted your hair all over me I’d roll around on that gross pillow that you favor so dearly… I digress)
Let’s take two examples using our AdventureWorks Database and pull back all the ProductIDs that have never been sold using two different methods… the tried, tested, and true LEFT OUTER JOIN and an operator that came on the scene in SQL SERVER 2005…EXCEPT.
FROM Production.Product A LEFT OUTER JOIN
Sales.SalesOrderDetail B ON A.ProductID = B.ProductID
WHERE B.ProductID IS NULL
EXCEPT Profiler Results:
CPU – 0
READS – 1132
WRITES – 0
Duration – 12
LEFT OUTER JOIN Profiler results:
CPU – 110
READS – 243
WRITES – 0
Duration – 111
So if we were just looking at this from a “who gets to the finish line first” point of view then the EXCEPT version of the query wins. One thing to note though is that the reads are 4 times as much in the EXCEPT as they are in the LEFT OUTER JOIN example. This is one of those situations where more reads actually equals better performance.
Now this is NOT an arguement or endorsement in favor of EXCEPT over LEFT OUTER JOIN (or NOT IN, NOT EXISTS, etc) as different situations will call for you use different operators to get the best bang for your buck. This is just another example on how two different methods to achieve the same goal can produce significantly different results (and execution plans as the EXCEPT did an INDEX SEEK where the LEFT OUTER JOIN did an INDEX SCAN)
CAUTION: One “Gotcha” or at least something to take note of is that EXCEPT returns any distinct values from the left query that are not also found on the right query… take note of the word DISTINCT.
Anyways… if you haven’t looked into EXCEPT (and INTERSECT) check out the Books Online information on the arguments.