Difference between except and not in sql server

except vs not in sql server
difference between not in and except in sql server

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

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

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

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)

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, Gender 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 * 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.

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-except-and-not-in.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-except-and-not-in_5.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists