7-36 複数テーブルと完全外部結合

SQLパズル

TableA    TableB    TableC
ID  C1    ID  C2    ID  C3
--  --    --  --    --  --
 1  A      1  D      1  G
 2  B      4  E      4  H
 3  C      5  F      6  I

TableA、TableB、TableCから以下の出力を行う。

出力結果
ID  C1    C2    C3
--  ----  ----  ----
 1  A     D     G
 2  B     null  null
 3  C     null  null
 4  null  E     H
 5  null  F     null
 6  null  null  I

データ作成スクリプト

create table tableA(
ID number(1),
C1 char(1));

insert into tableA values(1,'A');
insert into tableA values(2,'B');
insert into tableA values(3,'C');

create table tableB(
ID number(1),
C2 char(1));

insert into tableB values(1,'D');
insert into tableB values(4,'E');
insert into tableB values(5,'F');

create table tableC(
ID number(1),
C3 char(1));

insert into tableC values(1,'G');
insert into tableC values(4,'H');
insert into tableC values(6,'I');
commit;

SQL

col C1 for a4
col C2 for a4
col C3 for a4

--■■■unionと外部結合を使う方法■■■
select d.ID,
a.C1,b.C2,c.C3
from tableA a,tableB b,tableC c,
(select ID from tableA
union select ID from tableB
union select ID from tableC) d
where d.ID = a.ID(+)
  and d.ID = b.ID(+)
  and d.ID = c.ID(+)
order by d.ID;

--■■■full joinを使う方法■■■
select coalesce(a.ID,b.ID,c.ID) as ID,
a.C1,b.C2,c.C3
from tableA a
full join tableB b on (a.ID = b.ID)
full join tableC c on (c.ID in(a.ID,b.ID))
order by ID;

--■■■グループ化する方法■■■
select ID,
max(decode(no,1,Col)) as C1,
max(decode(no,2,Col)) as C2,
max(decode(no,3,Col)) as C3
from (select 1 as no,ID,C1 as Col from tableA
union all select 2,ID,C2 from tableB
union all select 3,ID,C3 from tableC)
group by ID
order by ID;

解説

full joinを使う方法では、
full joinとcoalesce関数を組み合わせてます。

---