Re: How to diff two tables?

fhuang_at_cs.umd.edu (Feng Huang) wrote:

>Hi, there,
>
>This question may have been asked before, if so please bear with me.
>
>I have two tables with identical structures. Let's call them tbl1 and tbl2.
>I'd like to find out what rows are common to both tables, what rows have
>the same key values but different for other columns, what rows only exist
>in tbl1, and only in tbl2?
>
>My objective is to do this efficiently. The tables contains about 10,000
>records each. One simple way is: for each record in tbl1, find a match in
>tbl2. And for each record in tbl2, find a match in tbl1. This is simple
>but dumb, any better ways?
>
>Your help would be appreciated.
>
>Thanks very much.
>
>-Feng
>fhuang_at_cs.umd.edu
>
Try using various set operations :

To see which entire rows exist in both tables use create view test_both_tbls as select * from tbl1 intersect
select * from tbl2;.
The view test_both_tbs will contain only rows that both tables have that are exactly the same.

To do the same with only the key value(s) use create view test_key_value as select keyvalue1,keyvalue2... from tbl1 intersect
select ( same values as first select ) from tbl2; This view will contain only the columns that are in both tables - to determine if the rest of the columns are different try create view see_samekey_but_different select (key columns ) from test_both_tbs minus
select (key columns) from test_key_value; This view will contain the key values for those rows where some other column does not match between tbl1 and tbl2.

Your other tests will be similar:
select * from tbl1 minus select * from tbl2 returns those rows in tbl1 that are not in tbl2 - and so on.....

Hope it helps,
( note for these to work be sure that the columns specified in the first select are the same as those in the second ).

To reply please remove the 'nospam' part of the address Received on Wed Sep 23 1998 - 00:00:00 CEST