Compare 2 tables data field by field and extract only unmatched rows Oracle

Question

I have below 2 tables, where I need to extract only unmatched rows from table A. If any column in table A having null values then we don't need to compare the same with table B column. How can I achieve A-B result?.

TableA minus TableB gives nearer result, but I need to check null constraint while comparing.

Also as the data's are millions, I need to perform some bulk operation. please help me with best way to do the same.

Answer 1

select
  a.id as a_id, b.id as b_id
,a.col1 as a_col1,b.col1 as b_col1
,a.col2 as a_col2,b.col2 as b_col2
,a.col3 as a_col3,b.col3 as b_col3
,a.col4 as a_col4,b.col4 as b_col4
,a.col5 as a_col5,b.col5 as b_col5
from a
     full outer join b
          on (b.id=a.id)
where 
   decode(a.col1,b.col1,0,1)=1
or decode(a.col2,b.col2,0,1)=1
or decode(a.col3,b.col3,0,1)=1
or decode(a.col4,b.col4,0,1)=1
or decode(a.col5,b.col5,0,1)=1

Full example with test data:

with
 a(id,col1,col2,col3,col4,col5) as (
    select 1,'Testcase42' ,'Testcase43'   ,date'1987-07-03' ,'test account'  ,919599636744 from dual union all
    select 2,'Thakur_1'   ,''             ,date'1990-08-05' ,''              ,919722100947 from dual union all
    select 3,'Thakur_3'   ,'Thakur_4'     ,date'1995-12-05' ,'test account'  ,919722100948 from dual 
)
,b(id,col1,col2,col3,col4,col5) as (
    select 1,'Testcase42' ,'Testcase43'   ,date'1987-07-03' ,'test account'  ,919599636744 from dual union all
    select 2,'Thakur_1'   ,'Thakur_2'             ,date'1990-08-05' ,'test account'              ,919722100947 from dual union all
    select 3,'Thakur_3'   ,'Thakur_4'     ,null             ,'test account'  ,919722100948 from dual 
)
select
  a.id as a_id, b.id as b_id
,a.col1 as a_col1,b.col1 as b_col1
,a.col2 as a_col2,b.col2 as b_col2
,a.col3 as a_col3,b.col3 as b_col3
,a.col4 as a_col4,b.col4 as b_col4
,a.col5 as a_col5,b.col5 as b_col5
from a
     inner join b
          on (a.id=b.id)
where 
   decode(a.col1,null,0,decode(a.col1,b.col1,0,1))=1
or decode(a.col2,null,0,decode(a.col2,b.col2,0,1))=1
or decode(a.col3,null,0,decode(a.col3,b.col3,0,1))=1
or decode(a.col4,null,0,decode(a.col4,b.col4,0,1))=1
or decode(a.col5,null,0,decode(a.col5,b.col5,0,1))=1;

      A_ID       B_ID A_COL1     B_COL1     A_COL2     B_COL2     A_COL3              B_COL3              A_COL4       B_COL4           A_COL5     B_COL5
---------- ---------- ---------- ---------- ---------- ---------- ------------------- ------------------- ------------ ------------ ---------- ----------
         3          3 Thakur_3   Thakur_3   Thakur_4   Thakur_4   1995-12-05 00:00:00                     test account test account 9.1972E+11 9.1972E+11

1 row selected.