[sql] How to compare two tables column by column in oracle

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.

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       

Used full outer join -- But it will not show - if its not matched -

SQL> desc aaa - its a table Name Null? Type

A1 NUMBER B1 VARCHAR2(10)

SQL> desc aaav -its a view Name Null? Type

A1 NUMBER B1 VARCHAR2(10)

SQL> select a.column_name,b.column_name from dba_tab_columns a full outer join dba_tab_columns b on a.column_name=b.column_name where a.TABLE_NAME='AAA' and B.table_name='AAAV';

COLUMN_NAME COLUMN_NAME

A1 A1 B1 B1

As an alternative which saves from full scanning each table twice and also gives you an easy way to tell which table had more rows with a combination of values than the other:

SELECT col1
     , col2
     -- (include all columns that you want to compare)
     , COUNT(src1) CNT1
     , COUNT(src2) CNT2
  FROM (SELECT a.col1
             , a.col2
             -- (include all columns that you want to compare)
             , 1 src1
             , TO_NUMBER(NULL) src2
          FROM tab_a a
         UNION ALL
        SELECT b.col1
             , b.col2
             -- (include all columns that you want to compare)
             , TO_NUMBER(NULL) src1
             , 2 src2
          FROM tab_b b
       )
 GROUP BY col1
        , col2
HAVING COUNT(src1) <> COUNT(src2) -- only show the combinations that don't match

Credit goes here: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1417403971710

SELECT *
  FROM (SELECT   table_name, COUNT (*) cnt
            FROM all_tab_columns
           WHERE owner IN ('OWNER_A')
        GROUP BY table_name) x,
       (SELECT   table_name, COUNT (*) cnt
            FROM all_tab_columns
           WHERE owner IN ('OWNER_B')
        GROUP BY table_name) y
 WHERE x.table_name = y.table_name AND x.cnt <> y.cnt;

Try to use 3rd party tool, such as SQL Data Examiner which compares Oracle databases and shows you differences.

Using the minus operator was working but also it was taking more time to execute which was not acceptable. I have a similar kind of requirement for data migration and I used the NOT IN operator for that. The modified query is :

select * 
from A 
where (emp_id,emp_name) not in 
   (select emp_id,emp_name from B) 
   union all 
select * from B 
where (emp_id,emp_name) not in 
   (select emp_id,emp_name from A); 

This query executed fast. Also you can add any number of columns in the select query. Only catch is that both tables should have the exact same table structure for this to be executed.