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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?