Interview Question of the Week #060 – What is the Difference Between EXCEPT Keyword and NOT IN?

Earlier this week, I had the opportunity to take many different interviews. Most of the DBA and Developers update their resume with various versions of the SQL Server. It seems like everybody had written SQL Server 2005 as one of their expertise. At this point of time, I remembered keyword EXCEPT which was introduced in SQL Server 2005 but not many people know about it. After interviewing, I realized that even after 11 years of release of SQL Server 2005 still most of the people do not know about EXCEPT keyword.

Question: What is the Difference Between EXCEPT and NOT IN?

Answer: The EXCEPT operator returns all of the distinct rows from the query to the left of the EXCEPT operator when there is 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 are not also returned 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 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 alias.

Example: (Both of the scripts returns 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

Following the execution plan which indicates EXCEPT and NOT IN uses same execution plan. Click on below image to see larger image.

Reference: Pinal Dave (https://blog.sqlauthority.com)