Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table table_a 
  (
    id int,
    column_a varchar(255),
    column_b varchar(255),
    column_c varchar(255)
  );

create table table_b 
  (
    id int,
    column_a varchar(255),
    column_b varchar(255),
    column_c varchar(255)
  );
insert into table_a values
  (1,'apple','pear','banana'),
  (2,'kiwi','blueberry','orange');

2 rows affected

insert into table_b values
  (1,'apple','pear','kale'),
  (2,'plum','corn','orange');
2 rows affected

with table_a_pivot as (
select * 
  from table_a
unpivot(
  val for column_name in (
    column_a as 'column_a',
    column_b as 'column_b',
    column_c as 'column_c'
  )
)
),
table_b_pivot as (
select * 
  from table_b
unpivot(
  val for column_name in (
    column_a as 'column_a',
    column_b as 'column_b',
    column_c as 'column_c'
  )
)
)
select table_a_pivot.id,
       table_b_pivot.column_name,
       table_a_pivot.val as a_value,
       table_b_pivot.val as b_value
  from table_a_pivot
 inner
  join table_b_pivot
    on table_a_pivot.id = table_b_pivot.id
   and table_a_pivot.column_name = table_b_pivot.column_name
   and table_a_pivot.val <> table_b_pivot.val;


ID
COLUMN_NAME
A_VALUE
B_VALUE


1
column_c
banana
kale

2
column_a
kiwi
plum

2
column_b
blueberry
corn


with combine_results as (
select 'Table_A' as origination,
        a.*
  from table_a a
 union 
select 'Table_B' as origination,
        b.*
  from table_b b)
select * 
  from combine_results


ORIGINATION
ID
COLUMN_A
COLUMN_B
COLUMN_C


Table_A
1
apple
pear
banana

Table_A
2
kiwi
blueberry
orange

Table_B
1
apple
pear
kale

Table_B
2
plum
corn
orange


with combine_results as (
select 'Table_A' as origination,
        a.*
  from table_a a
 union 
select 'Table_B' as origination,
        b.*
  from table_b b)
select * 
  from combine_results
unpivot(
  val for column_name in (
    column_a as 'column_a',
    column_b as 'column_b',
    column_c as 'column_c'
  )
)


ORIGINATION
ID
COLUMN_NAME
VAL


Table_A
1
column_a
apple

Table_A
1
column_b
pear

Table_A
1
column_c
banana

Table_A
2
column_a
kiwi

Table_A
2
column_b
blueberry

Table_A
2
column_c
orange

Table_B
1
column_a
apple

Table_B
1
column_b
pear

Table_B
1
column_c
kale

Table_B
2
column_a
plum

Table_B
2
column_b
corn

Table_B
2
column_c
orange

ORIGINATION	ID	COLUMN_A	COLUMN_B	COLUMN_C
Table_A	1	apple	pear	banana
Table_A	2	kiwi	blueberry	orange
Table_B	1	apple	pear	kale
Table_B	2	plum	corn	orange

ORIGINATION	ID	COLUMN_NAME	VAL
Table_A	1	column_a	apple
Table_A	1	column_b	pear
Table_A	1	column_c	banana
Table_A	2	column_a	kiwi
Table_A	2	column_b	blueberry
Table_A	2	column_c	orange
Table_B	1	column_a	apple
Table_B	1	column_b	pear
Table_B	1	column_c	kale
Table_B	2	column_a	plum
Table_B	2	column_b	corn
Table_B	2	column_c	orange