Re: How to diff two tables?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/09/23
Message-ID: <36091b9f.3775348_at_192.86.155.100>#1/1

A copy of this was sent to fhuang_at_cs.umd.edu (Feng Huang) (if that email address didn't require changing) On 23 Sep 1998 11:23:25 -0400, you 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?
>

easy to do 3 out of the 4 (and the 4'th will require another query).

lets say the tables have structures:

t1 ( pkey, data );
t2 ( pkey, data );

select t1.pkey, decode( t2.pkey, NULL, 'Only in T1',

                   decode( t1.data, t2.data, 'Same', 'Different Data' ) )

  from t1, t2
 where t1.pkey = t2.pkey(+)
/

That gives you
- every row in T1 with a mate in T2 with a column that says "Same" when they have the same data

• every row in T1 with a mate in T2 with a column that says "Different data" when they have diff data
• every row in T1 without a mate in T2 with a column that says "Only In T1"

You need to also have

select t2.pkey, 'Only In T1'
  from t2, t1
 where t2.pkey = t1.pkey(+)
   and t1.pkey IS NULL
/

So, after running the script:

create table t1 ( pkey int, data varchar2(25) ); create table t2 ( pkey int, data varchar2(25) );

insert into t1 values ( 1, 'data' );
insert into t2 values ( 1, 'data' );

insert into t1 values ( 2, 'data 1' );
insert into t2 values ( 2, 'data 2' );

insert into t1 values ( 3, 'data' );
insert into t2 values ( 4, 'data' );

select t1.pkey, decode( t2.pkey, NULL, 'Only in T1',

                   decode( t1.data, t2.data, 'Same', 'Different Data' ) )

  from t1, t2
 where t1.pkey = t2.pkey(+)
union all
select t2.pkey, 'Only In T2'
  from t2, t1
 where t2.pkey = t1.pkey(+)
   and t1.pkey IS NULL
/

I got the results:

      PKEY DECODE(T2.PKEY
---------- --------------

         1 Same
         2 Different Data
         3 Only in T1
         4 Only In T2





  

>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
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Wed Sep 23 1998 - 00:00:00 CEST