Except operator SQL Server 2005 -Find Duplicate values in multiple tables.

I have found a great way to find duplicate values with two tables in sql server. Suppose we have two tables t1 and t2 both have same fields like id,name.

And both table have following data.

T1 Data:
1 abc
2 xyz
3 pqr

T2 Data:
2 xyz

We need to find rows from table t1 which is not there in table t2.

Here is the simple way to do it.

select * from t1
select * from t2

Except operator return result by comparing two queries. It returns row of left query which are not in right query. So result for above queries will be as following

1 abc
3 pqr

The basic rules for combining the result sets of two queries that use EXCEPT are the following:
The number and the order of the columns must be the same in all queries.
The data types must be compatible.


  1. Nice post. Are there any differences in performance between using EXCEPT instead of NOT IN?


Post a Comment

Your feedback is very important to me. Please provide your feedback via putting comments.

Popular posts from this blog

How to create Rest API(Web API) with ASP.NET Core 1.0

Creating PDF with ASP.Net MVC and RazorPDF

Solution: gulp is not recognized as internal or external command- Visual Studio