SQL SERVER – 2005 Comparison EXCEPT operator vs. NOT IN

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&nbspIN SQL SERVER 2005
USE AdventureWorks;
GO
SELECT ProductID
FROM Production.Product
WHERE ProductID
NOT IN (
SELECT ProductID
FROM Production.WorkOrder);
GO

Following the execution plan which indicates EXCEPT and NOT IN uses same execution plan.

Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)