Oracle: Full outer join

Simple example

create table tq84_foj_a (
  id_a   number,
  txt_a  varchar2(10)
);

create table tq84_foj_b (
  id_b   number,
  txt_b  varchar2(10)
);

insert into tq84_foj_a values (1, 'abc');
insert into tq84_foj_a values (2, 'def');
insert into tq84_foj_a values (3, 'ghi');

insert into tq84_foj_b values (2, 'FOO');
insert into tq84_foj_b values (4, 'BAR');

select
  coalesce(a.id_a, b.id_b) id,
  a.txt_a,
  b.txt_b
from
  tq84_foj_a a full outer join
  tq84_foj_b b                   on id_a = id_b;

drop table tq84_foj_a purge;
drop table tq84_foj_b purge;

One table

create table tq84_foj (
  id_1    number,
  id_2    number,
  txt     varchar2(10)
);

insert into tq84_foj values (1, 1, '#');
insert into tq84_foj values (1, 2, '?');

insert into tq84_foj values (2, 1, 'abc');
insert into tq84_foj values (2, 2, 'def');
insert into tq84_foj values (2, 3, 'ghi');

insert into tq84_foj values (3, 2, 'FOO');
insert into tq84_foj values (3, 4, 'BAR');


select
  a.id_1, b.id_1,
  a.txt , b.txt
from
  tq84_foj a full outer join
  tq84_foj b on a.id_2 = b.id_2 and
                a.id_1 = 2      and
                b.id_1 = 3;
--      ID_1       ID_1 TXT        TXT
------------ ---------- ---------- ----------
--                    1            #
--                    1            ?
--                    2            abc
--                    2            def
--                    2            ghi
--         2          3 def        FOO
--                    3            BAR
--         2            ghi
--         2            abc
--         1            #
--         3            FOO
--         1            ?
--         3            BAR
                  

with a as (select * from tq84_foj where id_1 = 2),
     b as (select * from tq84_foj where id_1 = 3)
select
  a.id_1, b.id_1,
  a.txt , b.txt
from
  a full outer join b on a.id_2 = b.id_2;
--      ID_1       ID_1 TXT        TXT
------------ ---------- ---------- ----------
--         2            abc
--         2          3 def        FOO
--         2            ghi
--                    3            BAR

drop table tq84_foj purge;

Outer join and left join

create table tq84_lookup (
  id    number       primary key,
  txt   varchar2(10)
);

insert into tq84_lookup values (1, 'one');
insert into tq84_lookup values (2, 'two');
insert into tq84_lookup values (3, 'three');
insert into tq84_lookup values (4, 'four');
insert into tq84_lookup values (5, 'five');
insert into tq84_lookup values (6, 'six');

create table tq84_items (
  id_set    number,
  id_lookup number not null references tq84_lookup,
  txt       varchar2(10),
  primary key (id_set, id_lookup)
);

insert into tq84_items values (1, 1, '#');
insert into tq84_items values (1, 2, '?');

insert into tq84_items values (2, 2, '_');
insert into tq84_items values (2, 6, '!');

insert into tq84_items values (3, 2, 'abc');
insert into tq84_items values (3, 3, 'def');
insert into tq84_items values (3, 4, 'ghi');

insert into tq84_items values (4, 2, 'FOO');
insert into tq84_items values (4, 3, 'BAR');
insert into tq84_items values (4, 5, 'BAZ');

with i_3 as (select * from tq84_items where id_set = 3),
     i_4 as (select * from tq84_items where id_set = 4)
select
  nvl(i_3.id_lookup, i_4.id_lookup) id,
  nvl(l_3.txt      , l_4.txt      ) txt_lookup,
  i_3.txt                           txt_lookup_3,
  i_4.txt                           txt_lookup_4
from
  i_3                                               full outer join
  i_4             on i_3.id_lookup = i_4.id_lookup       left  join
  tq84_lookup l_3 on i_3.id_lookup = l_3.id              left  join
  tq84_lookup l_4 on i_4.id_lookup = l_4.id
;

drop table tq84_items  purge;
drop table tq84_lookup purge;

See also