FIndout the column name and the column value who is having different value

Question

I have two tables A and B having same structure.

for example : A have columns id,name ,adress and gender. Similary table B has same structure. ID is a link between these two tables. What I want is to print the ID , Column_name list whose values are different, A column values and B column values whose values are different from each other.

I have attached the image for the reference output

Answer 1

This gets your output in a format that is readable for humans and sql. This kind of thing can be done many ways to avoid redundancy, but this ought to give you a starting point.

Rationale: Unpivot both tables so you have a skinny result of the column name and value. Then you can join on the id and column name with an inequality condition to get the records which are different.

Fiddle: https://dbfiddle.uk/EnOa7U7g

Notes, be wary of:

1.) Nulls. "<>" Does not work for nulls, you'll have to handle that explicitly if your data is nullable.

2.) Orphans between the two tables, and datatypes. For simplicity I've inner joins, so if you can have id's in table_a but not table_b or vice versa you'll need to handle that in some appropriate way.

3.) Datatypes, overloading a column with information from many sometimes requires casting where Oracle can't do it implicitly.

Table_A

ID	COLUMN_A	COLUMN_B	COLUMN_C
1	apple	pear	banana
2	kiwi	blueberry	orange

Table_B

ID	COLUMN_A	COLUMN_B	COLUMN_C
1	apple	pear	kale
2	plum	corn	orange

Result

ID	COLUMN_NAME	A_VALUE	B_VALUE
1	column_c	banana	kale
2	column_a	kiwi	plum
2	column_b	blueberry	corn

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');

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

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;

Answer 2

The query works only if your columns are constant,

select A.id,
case when A.name != B.name and A.address != B.address and A.gender != B.gender then 'name, address, gender'
when A.name = B.name and A.address != B.address and A.gender != B.gender
then 'address, gender'
when A.name != B.name and A.address != B.address and A.gender = B.gender then 'name, address'
when A.name = B.name and A.address != B.address and A.gender = B.gender then 'address'
when A.name = B.name and A.address = B.address and A.gender != B.gender then 'gender'
when A.name != B.name and A.address = B.address and A.gender = B.gender then 'name' end as diff_columns, A.name, A.address, A.gender 
 from A
inner join b
on (A.id = B.id);