merge two oracle tables into one

Question

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

Search for How to do a FULL OUTER JOIN in Oracle. Littlefoot below has beaten to a suggested way of doing this. — TenG, Oct 27 '18 at 17:58

Oiale · Answer 1 · 2018-10-27T22:07:48.613

2

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>

edited Oct 27 '18 at 22:07

answered Oct 27 '18 at 21:52

Oiale

434
4
17

Even better... :-) – Bob Jarvis - Слава Україні Oct 28 '18 at 01:14
Please use explicit join syntax (ANSI compliant) and not thr old a,b syntax, especially avoid the Oracle's (+) syntax for outer joins, Oracle themselves recommend to not use it. – Kaushik Nayak Oct 28 '18 at 03:47
@Kaushik Nayak Hi! I just wonder why one shouldn't? Can you provide some links about that? – Oiale Oct 28 '18 at 08:08
The syntax is outdated for long ( more than 20 years ) . Please take a look at Oracle's documentation recommending to use ANSI syntax https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Joins.html#GUID-29A4584C-0741-4E6A-A89B-DCFAA222994A . and also answers to the question here: https://stackoverflow.com/questions/1193654/difference-between-oracles-plus-notation-and-ansi-join-notation – Kaushik Nayak Oct 28 '18 at 08:26

score 1 · Answer 2 · answered Oct 27 '18 at 17:56

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>

Ooooh. Good one... :-) – Bob Jarvis - Слава Україні Oct 28 '18 at 01:08 — Bob Jarvis - Слава Україні, Oct 28 '18 at 01:08

score 0 · Accepted Answer · answered Oct 28 '18 at 10:49

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.)

merge two oracle tables into one

3 Answers3