Interview Question of the Week #043 – What is the Difference Between EXCEPT operator vs. NOT IN

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.

Solarwinds

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

Interview Question of the Week #043 - What is the Difference Between EXCEPT operator vs. NOT IN SQLSERVER_EXCEPT_ExecPlan_small-1

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

Solarwinds
, ,
Previous Post
Interview Question of the Week #042 – How Does SPACE Function Works in SQL Server?
Next Post
Interview Question of the Week #044 – What is the difference of performance between SELECT and SET?

Related Posts

7 Comments. Leave new

  • Hi Pinal,

    Where can i view the data/schema of the tables used in the examples.

    Reply
  • nakulvachhrajani
    October 31, 2015 3:26 pm

    If it’s just one column, surely both EXCEPT and NOT IN are the same. However, if more columns are involved, then they are quite different. EXCEPT works on all columns of the result set whereas NOT IN would work on the column being referenced.

    Reply
  • You forgot the biggest difference — what happens if there is a NULL in the data. In that case except works as you would expect, but not in won’t return any rows.

    Reply
  • Shrikant Khode
    March 22, 2016 10:43 pm

    Hey Pinal,
    As per your explanation , ” Except ” and ” Not In ” both are same ?

    Reply
  • Having issue where an EXCEPT query works as expected in the query window, but when I put the same code into a Stored Procedure it returns many more records. Basically it is returning records where NULL is in the record, where in the query window it was correctly not returning these records where both datasets being compared both had a null in that column. Can you tell me how to correctly set the settings for my stored procedures so it works correctly with EXCEPT in relation to how it handles NULLS? I’m using SQL Server 2012 and server is in Compatability Level: SQL Server 2012(110). Thanks!

    Reply
  • Could you please explain the working of ‘AND’ & ‘OR’ Operator ?
    I this AND work as Intersect & ‘OR’ work as UNION

    Reply

Leave a Reply

Menu