Difference between except and not in sql server

Suggested Videos
Part 85 - Retry logic for deadlock exceptions
Part 86 - How to find blocking queries in sql server
Part 87 - SQL Server except operator



In this video we will discuss the difference between EXCEPT and NOT IN operators in SQL Server.



We will use the following 2 tables for this example.

The following query returns the rows from the left query that aren’t in the right query’s results.

Select Id, Name, Gender From TableA

Except

Select Id, Name, Gender From TableB

Result :


The same result can also be achieved using NOT IN operator.

Select Id, Name, Gender From TableA

Where Id NOT IN (Select Id from TableB)

So, what is the difference between EXCEPT and NOT IN operators
1. Except filters duplicates and returns only DISTINCT rows from the left query that aren’t in the right query’s results, where as NOT IN does not filter the duplicates.

Insert the following row into TableA

Insert into TableA values (1, 'Mark', 'Male')

Now execute the following EXCEPT query. Notice that we get only the DISTINCT rows

Select Id, Name, Gender From TableA

Except

Select Id, Name, Gender From TableB

Result:


Now execute the following query. Notice that the duplicate rows are not filtered.

Select Id, Name, Gender From TableA

Where Id NOT IN (Select Id from TableB)

Result:


2. EXCEPT operator expects the same number of columns in both the queries, where as NOT IN, compares a single column from the outer query with a single column from the subquery.

In the following example, the number of columns are different.

Select Id, Name, Gender From TableA

Except

Select Id, Name From TableB

The above query would produce the following error.
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

NOT IN, compares a single column from the outer query with a single column from subquery. 

In the following example, the subquery returns multiple columns

Select Id, Name, Gender From TableA

Where Id NOT IN (Select Id, Name from TableB)

Msg 116, Level 16, State 1, Line 2
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.