Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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 :
except operator 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:
except operator 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:
sql server not in example

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.

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.