Skip to Main Content
  • Questions
  • comparing two databases, put result into new table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 17, 2016 - 4:40 am UTC

Last updated: November 18, 2016 - 4:16 am UTC

Version: CORE 11.2.0.3.0 Production

Viewed 1000+ times

You Asked

Hi Tom

Thank you for having this service available

I'm trying to create an application that will allow me to compare data from two tables by a common ID

I would like to either:
put the results (non matches) into a new table
OR
remove the non matches from the original table

I am fairly new to apex and have had trouble finding a answer online so far. Any feedback and help are most appreciated

Thanks,
Steve

and Connor said...

You can use a full outer join to achieve this. Here's an example, assuming OBJECT_ID is the common key

SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 as select object_id, owner, object_name
  2  from dba_objects
  3  where rownum <= 10;

Table created.

SQL>
SQL>
SQL> drop table t2 purge;

Table dropped.

SQL> create table t2 as select object_id, owner, case when rownum = 3 then lower(object_name) else object_name end object_name
  2  from dba_objects
  3  where rownum <= 12;

Table created.

SQL>
SQL>
SQL> select
  2    t1.object_id   t1_obj,
  3    t2.object_id   t2_obj,
  4    t1.owner       t1_own,
  5    t2.owner       t2_own,
  6    t1.object_name t1_name,
  7    t2.object_name t2_name
  8  from t1
  9   full outer join t2
 10  on t1.object_id = t2.object_id
 11  where
 12     t1.object_id is null
 13  or t2.object_id is null
 14  or decode(t1.owner,t2.owner,1,0) = 0
 15  or decode(t1.object_name,t2.object_name,1,0) = 0
 16  ;

    T1_OBJ     T2_OBJ T1_OWN               T2_OWN               T1_NAME                        T2_NAME
---------- ---------- -------------------- -------------------- ------------------------------ ------------------------------
        32         32 SYS                  SYS                  CCOL$                          ccol$
                    6                      SYS                                                 C_TS#
                   48                      SYS                                                 I_COL1


So the three circumstances are catered for:

in T1, not in T2 => t2.obj is null
in T2, not in T1 => t1.obj is null
difference in data => the decode statements

I'm using DECODE because it handles nulls nicely as well, eg, I'll add some data:

SQL> insert into t2 values (-1,'x','y');

1 row created.

SQL> insert into t2 values (-2,'z',null);

1 row created.

SQL> insert into t1 values (-1,'x',null);

1 row created.

SQL> insert into t1 values (-2,'z',null);

1 row created.


So even though there are nulls (and null != null), in this case, we *probably* want to see "-1" because the object_name is differnet, and not see -2, because both are null.

SQL> select
  2    t1.object_id   t1_obj,
  3    t2.object_id   t2_obj,
  4    t1.owner       t1_own,
  5    t2.owner       t2_own,
  6    t1.object_name t1_name,
  7    t2.object_name t2_name
  8  from t1
  9   full outer join t2
 10  on t1.object_id = t2.object_id
 11  where
 12     t1.object_id is null
 13  or t2.object_id is null
 14  or decode(t1.owner,t2.owner,1,0) = 0
 15  or decode(t1.object_name,t2.object_name,1,0) = 0
 16  ;

    T1_OBJ     T2_OBJ T1_OWN               T2_OWN               T1_NAME                        T2_NAME
---------- ---------- -------------------- -------------------- ------------------------------ --------
        32         32 SYS                  SYS                  CCOL$                          ccol$
                    6                      SYS                                                 C_TS#
                   48                      SYS                                                 I_COL1
        -1         -1 x                    x                                                   y

4 rows selected.


You can tailor this into an insert statement or whatever you like.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you

Steve, November 17, 2016 - 11:09 pm UTC

Many thanks Connor
Cheers
Connor McDonald
November 18, 2016 - 4:16 am UTC

glad we could help