Question: What is the Difference Between EXCEPT operator vs. 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. The EXCEPT operator is equivalent of the Left Anti Semi Join. EXCEPT operator works the same way NOT IN. EXCEPTS returns any distinct values from the query to the left of the EXCEPT operand that do not also return from the right query.
Example of EXCEPT operator is displayed along Example of NOT IN. If you run both of the Query and compare the Execution plan it is exactly the same. EXCEPT and NOT IN does same functions and have the same execution plan, but EXCEPT has much simpler syntax. The row-by-row comparison provided by EXCEPT, combined with the number of rows being returned remaining consistent, provides compelling evidence that re-factored query is correct. EXCEPT works with * as well as aliases.
Example: (Both of the scripts returns the same number of rows)
-- SQL SERVER 2005 Method USE AdventureWorks; GO SELECT ProductID FROM Production.Product EXCEPT SELECT ProductID FROM Production.WorkOrder; GO
-- SQL SERVER 2000 Method which works IN SQL SERVER 2005 USE AdventureWorks; GO SELECT ProductID FROM Production.Product WHERE ProductID NOT IN ( SELECT ProductID FROM Production.WorkOrder); GO
Reference: Pinal Dave (https://blog.sqlauthority.com)