How to compare two tables column by column in oracle

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 * 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

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