Comparing 2 table data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Comparing 2 table data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-26 : 08:01:39

Sudha Raghavan writes "Hi All, I need to compare 2 tables data for MATCHING and i need output on the difference. That is Table A - Table B.

I do not want to use LEFT OUTER JOIN or NOT EXISTS because my tables are too huge and my database has got many tables to write this script for all where clause.

Can anybody help me on this???

Thanks In Advance.

Sudha Raghavan"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-26 : 08:03:30

quote:
I do not want to use LEFT OUTER JOIN or NOT EXISTS because my tables are too huge and my database has got many tables to write this script for all where clause.

Well, you can use NOT IN to check for the differing values, and it will operate MUCH, MUCH slower than using LEFT JOIN or NOT EXISTS. You could also use cursors, but man, glaciers move faster.

IOW, you're best off using the LEFT JOIN or NOT EXISTS, they are the fastest methods available.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-11-26 : 08:06:20

.

Damian

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-11-26 : 08:50:37

quote:

Well, you can use NOT IN to check for the differing values, and it will operate MUCH, MUCH slower than using LEFT JOIN or NOT EXISTS.

Care to give an example of that?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-26 : 09:11:30

I would, if I had one...

Probably used too many MUCH's in the original. Some quick tests with Northwind show NOT IN and NOT EXISTS generate the same plan, and LEFT JOIN is a little different with a slightly higher cost. I'm probably mentally harkening back to the 6.5 days and perhaps instances where unindexed columns abounded; NOT IN gave lower performance compared to LEFT JOINs and NOT EXISTS.

Hmmmm, can't find the foot-in-mouth emoticon, I could SWEAR I've used it before...guess this will have to suffice

But, I do take solace in the fact that I haven't found many instances where you use NOT IN Arnold.

I'm sure you've got a good reason.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-26 : 10:39:44

It depends on if you are comparing only keys, or if you are comparing all fields in each table assuming the keys in each record match.

If you want to see if records are missing in either table based on their primary keys, then NOT EXISTS can be fine. But if you need to compare 50 fields (some of which can be NULLs) that can be a cumbersome statement to write and can be slow.

Me? I use UNIONS and GROUP BY's to compare two different tables.

Example:

Table1
EmpID Name Dept Salary
1 Bill 10 $1000
2 Pete 20 $2000
3 Ed Null $2500
4 Joe 30 Null
5 Norm 20 $2000

Table2
EmpID Name Dept Salary
1 Bill 10 Null
2 JOhn 20 $2000
3 Ed Null $2500
4 Joe 10 Null

Assuming the ID fields are correlated, what you can do is:

SELECT EmpID, Min(TableName), Name, Dept, Salary
FROM
(
SELECT 'Table1' as TableName, Table1.*
FROM Table1
UNION ALL
SELECT 'Table2' as TableName, Table2.*
FROM Table2
) A
GROUP BY EmpID, Name, Dept, Salary
HAVING COUNT(*) = 1
ORDER BY EmpID, Min(TableName)

The above returns all records in each table that are not in the other, and all records in both tables that have changes in ANY fields. And you don't have to worry about NULLs. Plus, the output gives you all of the data so you can compare it easily.

Just an alternate way of doing compares between two tables -- works even better for comparing data in the same table (you don't need the UNION). give it a try.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources