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:
ID NAME
1 abc
2 xyz
3 pqr

T2 Data:
ID NAME
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
except
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

ID NAME
1 abc
3 pqr




NOTE:
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.
Share:

2 comments:

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

    ReplyDelete

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

Support this blog-Buy me a coffee

Buy me a coffeeBuy me a coffee
Search This Blog
Subscribe to my blog

  

My Mvp Profile
Follow us on facebook
Blog Archive
Total Pageviews