CODEWITHSUNDEEP
oracle-databaseoracle12c
sqlpractice
sqlpractice

Reputation: 141

Ways of Combining the data from Two tables in Oracle

I have the below sample data in two table :

Table1

Item Loc Hierarchy  Val4
I1   L1  Item       D1
I2   L2  Item       D2
I2   L3  Item       D3

Table2

 Loc Hierarchy Val1 Val2 Val3 
 L1  ' '       A1   B1   C1   
 L4  ' '       A4   B4   C4

Basically -

Single Loc can have entries in both Table1 and Table2

Particular Loc can have entry either in Table1 or Table2.

Expected Result- 

Item Loc Hierarchy Val1 Val2 Val3 Val4
I1   L1  Item      A1    B1   C1   D1
I2   L2  Item      null null null  D2
I2   L3  Item      null null null  D3
     L4  ' '       A4   B4   C4   null

How can I combine the data and achieve the desired result? By Full outer Join? Is there any other alternative methods which are performance efficient OR a way of boosting the full outer join query. As this is a subset of a bigger query therefore once I achieve this result I have to use this data set to update another table which has some 100M records( hence looking for performance efficient blocks).

Thanks in Advance!

Upvotes: 0

Views: 31

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

Looks like full outer join:

SQL> with
  2  tab1 (item, loc, hierarchy, val4) as
  3    (select 'I1', 'L1', 'Item', 'D1' from dual union all
  4     select 'I2', 'L2', 'Item', 'D2' from dual union all
  5     select 'I2', 'L3', 'Item', 'D3' from dual
  6    ),
  7  tab2 (loc, hierarchy, val1, val2, val3) as
  8    (select 'L1', null, 'A1', 'B1', 'C1' from dual union all
  9     select 'L4', null, 'A4', 'B4', 'C4' from dual
 10    )
 11  select a.item,
 12    nvl(a.loc, b.loc) loc,
 13    nvl(a.hierarchy, b.hierarchy) hierarchy,
 14    b.val1,
 15    b.val2,
 16    b.val3, a.val4
 17  from tab1 a full outer join tab2 b on a.loc = b.loc
 18  order by item, loc;

ITEM LOC HIER VAL1 VAL2 VAL3 VAL4
---- --- ---- ---- ---- ---- ----
I1   L1  Item A1   B1   C1   D1
I2   L2  Item                D2
I2   L3  Item                D3
     L4       A4   B4   C4

SQL>

Upvotes: 2

Related Questions