Optimize query that compares two tables with similar schema in different databases

I have two different tables with similar schema in different database. What is the best way to compare records between these two tables. I need to find out- records that exists in first table whose corresponding record does not exist in second table filtering records from the first table with some where clauses. So far I have come with this SQL construct:

Select t1_col1, t1_ col2 from table1
where t1_col1=<condition> AND 
t1_col2=<> AND
NOT EXISTS 
(SELECT * FROM
table2
WHERE
t1_col1=t2_col1 AND
t1_col2=t2_col2)

Is there a better way to do this?

This above query seems fine but I suspect it is doing row by row co开发者_Python百科mparison without evaluating the conditions in the first part of the query because the first part of the query will reduce the resultset very much. Is this happening?

Just use except keyword!!!

Select t1_col1, t1_ col2 from table1
    where t1_col1=<condition> AND 
    t1_col2=<condition> 
except
SELECT t2_col1, t2_ col2 FROM table2

It returns any distinct values from the query to the left of the EXCEPT operand that are not also returned from the right query.

For more information on MSDN

If the data in both table are expected to have the same primary key, you can use IN keyword to filter those are not found in the other table. This could be the simplest way.

If you are open to third party tools like Redgate Data Compare you can try it, it's a very nice tool. Visual Studio 2010 Ultimate edition also have this feature.