Except VS Not In SQL Server

Introduction

EXCEPT operator was introduced in SQL Server in 2005. 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 is similar as NOT IN with DISTINCT queries. The EXCEPT operator is equivalent of the Left Anti Semi Join.

 

NOT IN will return all rows from left hand side table which are not present in right hand side table but it will not remove duplicate rows from the result.

Let us take an example:

CREATE TABLE# NewData(ID INT, Name[nvarchar](30));
CREATE TABLE# ExistingData(ID INT, Name[nvarchar](30));

INSERT INTO# NewData
    (ID, Name)
VALUES
    (8, 'Pankaj'), (9, 'Rahul'), (10, 'Sanjeev'), (1, 'Sandeep'), (3, 'Priya'), (8, 'Deepak');

INSERT INTO# ExistingData
    (ID, Name)
VALUES
    (1, 'Sandeep'), (2, 'Neeraj'), (3, 'Priya'), (4, 'Omi'), (5, 'Divyanshu');


--EXCEPT filters
for DISTINCT values
SELECT nc.ID FROM# NewData AS nc
EXCEPT
SELECT ec.ID FROM# ExistingData AS ec

--NOT IN returns all values without filtering
SELECT nc.ID FROM# NewData AS nc
WHERE ID NOT IN(SELECT ec.ID FROM# ExistingData AS ec)

DROP TABLE# NewData;

DROP TABLE# ExistingData;

Output:

In above result first table is result of EXIST operator and second table is result of NOT IN operator. It is clear that EXIST operator always return the distinct result but NOT IN doesn’t remove duplicate value.

Let’s check the execution plan of both query.

Execution Plan

Execution plan of EXIST and NOTIN is almost similar, difference is that EXIST perform distinct sort for left side table.

If we performed the NOTIN operation with DISTINCT command then both query produce same result.

Example 1

SELECT nc.ID FROM# NewData AS nc
EXCEPT
SELECT ec.ID FROM# ExistingData AS ec

--NOT IN returns all values without filtering
SELECT DISTINCT nc.ID FROM# NewData AS nc
WHERE ID NOT IN(SELECT ec.ID FROM# ExistingData AS ec)

Output

Example 2

Now try to execute the following query and examine the output.

--EXCEPT filters for DISTINCT values
SELECT * FROM# NewData AS nc
EXCEPT
SELECT ec.ID FROM# ExistingData AS ec

--NOT IN returns all values without filtering
SELECT * FROM# NewData AS nc
WHERE ID NOT IN(SELECT ec.ID FROM# ExistingData AS ec)

Output

When we execute above query, SQL Server throw an error that “All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists”. This error indicate that number of column for both table (outer table and inner table) must be equal.

Example 3

Now first insert some NULL values for both columns in “#ExistingData” table and after that execute both query and examine the result.

CREATE TABLE# NewData(ID INT, Name[nvarchar](30));
CREATE TABLE# ExistingData(ID INT, Name[nvarchar](30));

INSERT INTO# NewData
    (ID, Name)
VALUES
    (8, 'Pankaj'), (9, 'Rahul'), (10, 'Sanjeev'), (1, 'Sandeep'), (3, 'Priya'), (8, 'Deepak');

INSERT INTO# ExistingData
    (ID, Name)
VALUES
    (1, 'Sandeep'), (2, 'Neeraj'), (3, 'Priya'), (NULL, NULL), (4, 'Omi'), (5, 'Divyanshu');


--EXCEPT filters
for DISTINCT values
SELECT nc.ID FROM# NewData AS nc
EXCEPT
SELECT ec.ID FROM# ExistingData AS ec

--NOT IN returns all values without filtering
SELECT nc.ID FROM# NewData AS nc
WHERE ID NOT IN(SELECT ec.ID FROM# ExistingData AS ec)


DROP TABLE# NewData;

DROP TABLE# ExistingData;

Output:

OOPS! What is going wrong ? Why doesn’t exist any result for NOT IN command ?

We know that NOT IN command just  work like an “AND” operator. It means,

WHERE ID NOT IN (SELECT ec.ID FROM #ExistingData AS ec)

Above condition is similar as below condition.

WHERE ID !=1 AND ID !=2 AND ID !=3 AND ID !=NULL AND ID !=4 AND ID !=5

Let us take some example.

When Value of ID is 8,

So 8!=1 = True , 8!=2 = True, 8!=3 = True , 8!=NULL = False , 8!=4 = True, 8!=5 = True

ANDING of all these result return FALSE,

 

True AND True AND True AND False AND True AND True= False

 

When Value of ID is 9,

So 9!=1 = True , 9!=2 = True, 9!=3 = True , 9!=NULL = False , 9!=4 = True, 9!=5 = True

ANDING of all these result return FALSE,

True AND True AND True AND False AND True AND True= False

 

When Value of ID is 10,

So 10!=1 = True , 10!=2 = True, 10!=3 = True , 10!=NULL = False , 10!=4 = True, 10!=5 = True

ANDING of all these result return FALSE,

True AND True AND True AND False AND True AND True= False

 

When Value of ID is 1,

So 1!=1 = False , 1!=2 = True, 1!=3 = True , 1!=NULL = False , 1!=4 = True, 1!=5 = True

ANDING of all these result return FALSE,

False AND True AND True AND False AND True AND True= False

 

When Value of ID is 3,

So 3!=1 = True , 3!=2 = True, 3!=3 = False , 3!=NULL = False , 3!=4 = True, 3!=5 = True

ANDING of all these result return FALSE,

True AND True AND False AND False AND True AND True= False

 

When Value of ID is 8,

 

So 8!=1 = True , 8!=2 = True, 8!=3 = True , 8!=NULL = False , 8!=4 = True, 8!=5 = True

ANDING of all these result return FALSE,

True AND True AND True AND False AND True AND True= False

We can see that we are getting always FALSE for each condition because we can’t compare any value with a NULL value, if we try to compare any value with NULL , it will always return FALSE. So NOTIN always return FALSE if it contain any NULL comparison.

 

Now we understand that how EXIST command work:

EXIST command only check that given value exist in a group of value or not.

SELECT nc.ID FROM #NewData AS nc
EXCEPT
SELECT ec.ID FROM #ExistingData AS ec

Above query is similar as below query.

SELECT nc.ID FROM #NewData AS nc
EXCEPT
(1,2,3,NULL,4,5)

However, if the right-hand table contains a NULL in the values being filtered by NOT IN, an empty result set is returned, potentially giving unexpected results but Except only find that value of left side table exist in right hand table or not, so Except never return a unexpected result.