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.

• How to Use EXCEPT Operator with an example in Sql

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.

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,

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’)

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

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)

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.

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

The post Difference Between EXCEPT and NOT IN appeared first on Software Development | Programming Tutorials.