Friday, July 6, 2007

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?


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

Search This Blog
About Me
My Photo

Full Stack Developer,Blogger,Microsoft Mvp, Mentor, Life long learner, 10+ Years of Experience for JavaScript, web and Microsoft technologies. ASP.NET MVC,Web Forms,C#,AngularJs,Golang
Mvp profile
Subscribe to my blog

Enter your email address:

Follow us on facebook
Blog Archive
Total Pageviews