CODEWITHSUNDEEP
sqloracle-databaseoracle11g
AlienDarkside
AlienDarkside

Reputation: 1

Getting a specific output from 2 tables

I have 2 tables :

tab_1 :

ID      VAL
1       Y
2       N
3       Y

tab_2 :

ID      VAL
2       N
3       X
4       Y

I want to get the final output like 

ID     Operation
1      INSERT
2      EQUAL
3      DIFF
4      DEL

I am in very basic level in joins so need some help in explanation/understanding this type of functionalities.Thanks in advance.

Upvotes: 0

Views: 33

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

It appears that you want a full outer join between the two tables; and then a case expression to compare the val columns (both their existence and values) in both tables. Maybe something like:

-- CTEs for sample data
with tab_1 (ID, VAL) as (
            select 1, 'Y' from dual
  union all select 2, 'N' from dual
  union all select 3, 'Y' from dual
),
tab_2 (ID, VAL) as (
            select 2, 'N' from dual
  union all select 3, 'X' from dual
  union all select 4, 'Y' from dual
)
-- actual query
select coalesce(t1.id, t2.id) as id,
  case
    when t1.id is null then 'DEL'
    when t2.id is null then 'INSERT'
    when t2.val = t1.val then 'EQUAL'
    else 'DIFF'
  end as operation
from tab_1 t1
full outer join tab_2 t2 on t2.id = t1.id
order by id;

        ID OPERATION
---------- ---------
         1 INSERT   
         2 EQUAL    
         3 DIFF     
         4 DEL

Upvotes: 2

Related Questions