How to compare two tables column by column in oracle

Question

I have two similar tables in oracle in two different databases. For example : my table name is EMPLOYEE and primary key is employee id. The same table with same columns(say 50 columns are is avlbl in two databases and two databases are linked.

I want to compare these two tables column by column and find out which records are not matching. i want the specific column in each row in two tables that are not matching.

Answer 1

It won't be fast, and there will be a lot for you to type (unless you generate the SQL from user_tab_columns), but here is what I use when I need to compare two tables row-by-row and column-by-column.

The query will return all rows that

• Exists in table1 but not in table2
• Exists in table2 but not in table1
• Exists in both tables, but have at least one column with a different value

(common identical rows will be excluded).

"PK" is the column(s) that make up your primary key. "a" will contain A if the present row exists in table1. "b" will contain B if the present row exists in table2.

select pk
      ,decode(a.rowid, null, null, 'A') as a
      ,decode(b.rowid, null, null, 'B') as b
      ,a.col1, b.col1
      ,a.col2, b.col2
      ,a.col3, b.col3
      ,...
  from table1 a 
  full outer 
  join table2 b using(pk)
 where decode(a.col1, b.col1, 1, 0) = 0
    or decode(a.col2, b.col2, 1, 0) = 0
    or decode(a.col3, b.col3, 1, 0) = 0
    or ...;

Edit Added example code to show the difference described in comment. Whenever one of the values contains NULL, the result will be different.

with a as(
   select 0    as col1 from dual union all
   select 1    as col1 from dual union all
   select null as col1 from dual
)
,b as(
   select 1    as col1 from dual union all
   select 2    as col1 from dual union all
   select null as col1 from dual
)   
select a.col1
      ,b.col1
      ,decode(a.col1, b.col1, 'Same', 'Different') as approach_1
      ,case when a.col1 <> b.col1 then 'Different' else 'Same' end as approach_2       
  from a,b
 order 
    by a.col1
      ,b.col1;    




col1   col1_1   approach_1  approach_2
====   ======   ==========  ==========
  0        1    Different   Different  
  0        2    Different   Different  
  0      null   Different   Same         <--- 
  1        1    Same        Same       
  1        2    Different   Different  
  1      null   Different   Same         <---
null       1    Different   Same         <---
null       2    Different   Same         <---
null     null   Same        Same       

Answer 2

select *
from 
(
( select * from TableInSchema1
  minus 
  select * from TableInSchema2)
union all
( select * from TableInSchema2
  minus
  select * from TableInSchema1)
)

should do the trick if you want to solve this with a query