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.
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
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.