Comparing 2 tables

Thread view:

m - 28 Mar 2008 06:00 GMT

Hi All

I need to compare 2 tables ( I need to eliminate 2 columns in the
table while comparing, they are dates and userids which wil be
different. )

Example:

TableA( colA, colB, colC, colD, colE)
TableB( colA, colB, colC, colD, colE)

I need to compare TableA and TableB , for me they are equal if
contents of colA, colB, colC are same , need not compare colD and
colE.

I write jdbc program which reads tables and compare row by row, just I
think it is laborious. Is there a better way of doing this. Please let
me know.

Thanks

Reply to this Message

David Harper - 28 Mar 2008 08:05 GMT

> Hi All
>
[quoted text clipped - 14 lines]
> think it is laborious. Is there a better way of doing this. Please let
> me know.

You may want to consider a left join between the two tables, along the
lines of

select TableA.*,TableB.* from TableA left join TableB on
(TableA.colA = TableB.colA and TableA.colB = TableB.colB and
TableA.colC = TableB.colC)
where TableB.colD is null or TableB.colE is null;

This will show you which rows in TableA are not equal (by your criteria)
to rows in TableB.

You probably need to repeat the query exchanging TableA and TableB to
find rows in TableB which are not equal to rows in TableA.

This assumes, of course, that the triples (colA,colB,colC) are unique in
each table i.e. there is only one row in TableA with any given
combination of (colA,colB,colC) and likewise with TableB.

David Harper
Cambridge, England

Reply to this Message

Roedy Green - 28 Mar 2008 13:33 GMT

>I write jdbc program which reads tables and compare row by row, just I
>think it is laborious. Is there a better way of doing this. Please let
>me know.

do it in SQL with a where clause.. Presumably you want only that
match or differ.

This will probably be faster because no records have to be transported
to your application which may be running on a different box.

Signature

Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com

Reply to this Message

Lew - 29 Mar 2008 03:23 GMT

>> I write jdbc program which reads tables and compare row by row, just I
>> think it is laborious. Is there a better way of doing this. Please let
[quoted text clipped - 5 lines]
> This will probably be faster because no records have to be transported
> to your application which may be running on a different box.

Given tables A and B that share a primary key (PK):
CREATE TABLE A ( id INTEGER PRIMARY KEY, etc. )
CREATE TABLE B ( id INTEGER PRIMARY KEY, etc. )

SELECT COUNT(*) FROM A WHERE id NOT IN (SELECT id FROM B);
and vice versa.

Signature

Lew

Reply to this Message

Comparing 2 tables

Free Magazines