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.

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

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

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

[krzysztofs]

Going slowly to the end of your course. Each video is fantastic. You are greatest teacher in the world. Thank U for educating community.

[PhilShnider]

Thank you thank you thank you thank you very very much keep up good work

[peterl]

Great tutorial as always. Will you do any client side tutorial? AngularJS would be much appreciated

[dhruvghosh]

Sir, intersect is also used for getting common record from 2 tables.
Can we use intersect over except in this case

[davidespada]

Nice tutorial, could you do videos about asp.net membership (its will work in both Forms & MVC ), its important and useful for all

[tushardeepsingh]

can we say where not exists works same way as except and ' not in ' operator

[satyajitbiswal]

ANOTHER ONE DIFFERNCE IS EXCEPTS /MINUS OPERTOR CAN CATCH NULL BUT NOT IN OPERATOR CAN'T CATCH NULL VALUE

[iliashterev]

NOT IN has a major drawback if the column has NULL values. Basically gives totally wrong results.

[ahmedmansour]

Summary :

Difference Between Except, Not in

EXCEPT is generally faster, especially for large datasets.
NOT IN can be slower, particularly with large subqueries.

EXCEPT includes NULL values in the result.
NOT IN excludes rows if the subquery returns any NULL values.

EXCEPT compares all selected columns.
NOT IN typically compares a single column against a subquery. --> where id[a signleColumn] not in (select id from <tableName>))[against a subquery.]

EXCEPT returns distinct rows.
NOT IN may return duplicates unless explicitly removed.

EXCEPT is part of set operations (like UNION, INTERSECT).
NOT IN is a subquery within a WHERE clause.