Difference Between EXCEPT and NOT IN

Difference Between EXCEPT and NOT IN

In this post, we will discuss the difference between EXCEPT and NOT IN operators in SQL server. This is the continuation of the previous post So please read the below post.

In the above post, we discussed that the EXCEPT operator returns all the rows from the left query that are not in the right query results. Not In operator also does exactly the same thing. Let’s understand this with an example here.

We’ve got two tables, TblCustomerTarget and TblCustomerSource, both the tables have got the same number and type of columns.
1
If you look at the data, the customer rows 3 and 4 are present in both the tables.

Now, what we want to do is? Write a query that’s going to return us only the rows that are present in Table TblCustomerTarget, but not in Table TblCustomerSource.

To achieve this, we can either use the EXCEPT operator or not in the operator. Let’s look at that in action.

Now let’s use the EXCEPT operator

Select Id, CustomerName, Gender
From TblCustomerTarget
Except
Select Id, CustomerName, Gender
From TblCustomerSource

So if we exclude this query, we should get only the rows that are present in table A but not in table B,

1

Now we can achieve this exact same thing using not In operator as well. Let’s see how to do it using them, not in the operator.

Select Id, CustomerName, Gender From TblCustomerTarget
Where Id NOT IN (Select Id from TblCustomerSource)

So the obvious next question that comes to mind is, what’s the difference between EXCEPT and not in operators? There are two key difference-

1. Except operator filters, duplicates, and returns, only distinct rows from the left query that are not in the right query results whereas not in does not filter the duplicates.

Let’s understand this difference with an example. At the moment. If you look at the data that we have got in table TblCustomerTarget, we don’t have any duplicate rows. Now, what I’m going to do is insert another record for Dr. Jacob So we will have the same Id name and gender. So let’s execute this insert query right here.

INSERT [dbo].[TblCustomerTarget] ([Id], [CustomerName], [Gender]) VALUES (1, N’Dr. Jacob’, N’Male’)

2

Now when we execute except query notice, we get only one row for Dr. Jacob. That means the duplicates are filtered.

1

whereas when we execute the Not In the query,
we get the duplicates. So the primary difference is that except filters the duplicates, whereas Not In we will not filter the duplicates.

Select Id, CustomerName, Gender From TblCustomerTarget
Where Id NOT IN (Select Id from TblCustomerSource)

3

2. Another difference is that except Operator expects the same number of columns in both the queries, whereas not it compares a single column from an outer query with a single column from a subquery.

So if you look at the except operator, notice we have got 2 select queries, on the left side of the except operator and on the right side of the except operator.

4

So those are the key differences between except and not in operators in SQL server.

Ashok Patel

I'm an electronic engineer working in a multinational company,having good experience on Electronics and electrical engineers design and oversee production of electronic equipment such as radios, televisions, computers, washing machines and telecommunication systems.I like to do RND and Research.I also have hands on experience graphic design software and in web designing having great command on ASP.NET, HTML5, JavaScript, T-SQL, JQuery.

Add comment

Your Header Sidebar area is currently empty. Hurry up and add some widgets.