In this post, we will discuss the difference between the EXCEPT and NOT IN operators in SQL Server. This is a continuation of the previous post, so please read the post below.
In the previous post, we discussed that the EXCEPT operator returns all the rows from the left query that are not in the right query results. The NOT IN operator also performs the same function. Let's illustrate this with an example.
We have two tables, TblCustomerTarget and TblCustomerSource, both containing the same number and type of columns.
If you look at the data, you'll notice that customer rows 3 and 4 exist in both tables. Now, what we aim to do is? Compose a query that will only return the rows present in Table TblCustomerTarget but not in Table TblCustomerSource.To accomplish this, we can utilize either the EXCEPT operator or the NOT IN operator. Let's observe this in action.
Now, let's employ 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 the EXCEPT and NOT IN operators? There are two key differences:
1. The 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 duplicates.
Let's understand this difference with an example. Currently, if you examine the data in table TblCustomerTarget, you'll notice that 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. 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.
However, when we execute the NOT IN query, we encounter duplicates. So, the primary difference is that EXCEPT filters the duplicates, whereas NOT IN does not filter the duplicates.
Select Id, CustomerName, Gender From TblCustomerTarget Where Id NOT IN (Select Id from TblCustomerSource)
2. Another difference is that the EXCEPT operator expects the same number of columns in both queries, whereas NOT IN compares a single column from an outer query with a single column from a subquery.
If you examine the EXCEPT operator, you'll notice that we have two select queries: one on the left side and one on the right side of the EXCEPT operator.
So those are the key differences between the EXCEPT and NOT IN operators in SQL Server.