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.
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
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.
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.
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.