CODEWITHSUNDEEP
sqloracle-database
Saad Malak
Saad Malak

Reputation: 3

merge two oracle tables into one

I've two oracle tables

first

itmnum  qty1
1       10
2       5
3       7
5       9

second 
itmnum  qty2
2       3
3       9
4       12

need the output like

itmnum qty1  qty2
1      10
2      5     3
3      7     9
4            12
5      5

thanks

Upvotes: 0

Views: 89

Answers (3)

William Robertson
William Robertson

Reputation: 16001

What you want is a full outer join:

with
  first_table (itmnum, qty1) as
    ( select 1, 10 from dual union all
      select 2, 5  from dual union all
      select 3, 7  from dual union all
      select 5, 9  from dual) 
, second_table (itmnum, qty2) as
    ( select 2, 3  from dual union all
      select 3, 9  from dual union all
      select 4, 12 from dual )
-- start here
select coalesce(f.itmnum,s.itmnum) as itmnum
     , f.qty1, s.qty2
from   first_table f
       full outer join second_table s
            on  s.itmnum = f.itmnum
order by 1;

    ITMNUM       QTY1       QTY2
---------- ---------- ----------
         1         10 
         2          5          3
         3          7          9
         4                    12
         5          9

(I'm breaking my own rule here by including the optional outer keyword which I usually skip for inner and outer joins, but full joins are rare enough that I think it might be more confusing without it.)

Upvotes: 0

Oiale
Oiale

Reputation: 434

One more option:

SQL> create table test1 (
2  itmnum number(10),
3  qty1 number(10));
4  create table test2 (
5  itmnum number(10),
6  qty2 number(10));
7  insert into test1 (itmnum, qty1) values (1,10);
8  insert into test1 (itmnum, qty1) values (2,5);
9  insert into test1 (itmnum, qty1) values (3,7);
10 insert into test1 (itmnum, qty1) values (5,9);
11 insert into test2 (itmnum, qty2) values (2,3);
12 insert into test2 (itmnum, qty2) values (3,9);
13 insert into test2 (itmnum, qty2) values (4,12);

SQL>select t1.itmnum, t1.qty1, t2.qty2
1  from test1 t1, test2 t2
2  where t1.itmnum = t2.itmnum(+)
3  union all
4  select t2.itmnum, t1.qty1, t2.qty2
5  from test1 t1, test2 t2
6  where t1.itmnum(+) = t2.itmnum
7  and   t1.itmnum is null
8  order by itmnum

    ITMNUM       QTY1       QTY2
---------- ---------- ----------
         1         10
         2          5          3
         3          7          9
         4                    12
         5          9
SQL>

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143103

Here's one option:

  • first and second are your current tables
  • all_nums selects all distinct itmnums from both tables
  • using outer join of first and second with all_nums, you get the result

.

SQL> with
  2    first (itmnum, qty1) as
  3      (select 1, 10 from dual union all
  4       select 2, 5 from dual union all
  5       select 3, 7 from dual union all
  6       select 5, 9 from dual),
  7    second (itmnum, qty2) as
  8      (select 2, 3 from dual union all
  9       select 3, 9 from dual union all
 10       select 4, 12 from dual),
 11  -- start here
 12    all_nums as
 13      (select itmnum from first union
 14       select itmnum from second
 15      )
 16  select a.itmnum, f.qty1, s.qty2
 17  from all_nums a left join first f on f.itmnum = a.itmnum
 18                  left join second s on s.itmnum = a.itmnum
 19  order by a.itmnum;

    ITMNUM       QTY1       QTY2
---------- ---------- ----------
         1         10
         2          5          3
         3          7          9
         4                    12
         5          9

SQL>

Upvotes: 1

Related Questions