During SQLPass summit, I was asked following question by one of the attendees. The question was about Performance Comparison of Except vs NOT IN. Let us quickly discuss this question.
Question: Which of the operator is better for query performance – EXCEPT or NOT IN?
Answer: The EXCEPT operator returns all of the distinct rows from the query to the left of the EXCEPT operator when there are no matching rows in the right query. EXCEPT operator works the same way NOT IN.
In simple words, there is absolutely no difference between how these operators work and there is absolutely no difference in the performance. They are just the same operators with the different name. Let us prove this with a simple example. You can run following two different queries with different operators and see how they perform exactly the same.
-- EXCEPT USE AdventureWorks2014; GO SELECT ProductID FROM Production.Product EXCEPT SELECT ProductID FROM Production.WorkOrder; GO -- NOT IN USE AdventureWorks2014; GO SELECT ProductID FROM Production.Product WHERE ProductID NOT IN ( SELECT ProductID FROM Production.WorkOrder); GO
When you run above script, it gives following execution plan.
Upon carefully observing that you can see that execution plan is absolutely same for both the queries. This essentially proves that both the operators give the same results and the same performance.
Reference: Pinal Dave (https://blog.sqlauthority.com)