4-1 インラインビューで完全外部結合

SQLパズル

親テーブル
Code  名前
----  --------
   1  織田信長
   2  豊臣秀吉
   3  浅井長政

男子テーブル
Code  Seq  名前
----  ---  ------
   1    1  信忠
   1    2  信雄
   1    3  信孝
   2    1  鶴松
   2    2  秀頼
   3    1  万福丸

女子テーブル
Code  Seq  名前
----  ---  ----
   1    1  五徳
   3    1  茶々
   3    2  初
   3    3  江

親テーブルと男子テーブルは1対Nの関係で、
親テーブルと女子テーブルも1対Nの関係です。

これらのテーブル同士を結合し、下記を出力する。

出力結果
Code  名前      男子名  女子名
----  --------  ------  ------
   1  織田信長  信忠    五徳
   1  織田信長  信雄    null
   1  織田信長  信孝    null
   2  豊臣秀吉  鶴松    null
   2  豊臣秀吉  秀頼    null
   3  浅井長政  万福丸  茶々
   3  浅井長政  初      null
   3  浅井長政  江      null

こちらを参考にさせていただきました

データ作成スクリプト

create table 親(
code number(1),
名前 varchar2(8));

create table 男子(
code number(1),
seq  number(1),
名前 varchar2(8));

create table 女子 as select * from 男子;

insert into 親   values(1,'織田信長');
insert into 男子 values(1,1,'信忠');
insert into 男子 values(1,2,'信雄');
insert into 男子 values(1,3,'信孝');
insert into 女子 values(1,1,'五徳');
insert into 親   values(2,'豊臣秀吉');
insert into 男子 values(2,1,'鶴松');
insert into 男子 values(2,2,'秀頼');
insert into 親   values(3,'浅井長政');
insert into 男子 values(3,1,'万福丸');
insert into 女子 values(3,1,'茶々');
insert into 女子 values(3,2,'初');
insert into 女子 values(3,3,'江');
commit;

SQL

--■■■unionで完全外部結合を代用する方法■■■
select a.code,a.名前,b.男子名,b.女子名
from 親 a,(select c.code as code,
             c.seq as seq,
             c.名前 as 男子名,
             d.名前 as 女子名
             from 男子 c,女子 d
             where c.code=d.code(+)
               and c.seq=d.seq(+)
             union select d.code as code,
             d.seq as seq,
             c.名前 as 男子名,
             d.名前 as 女子名
             from 男子 c,女子 d
             where c.code(+)=d.code
               and c.seq(+)=d.seq) b
where a.code = b.code
order by a.code,b.seq;

--■■■unionで完全外部結合を代用する方法(スカラー問い合わせを使用)■■■
select a.code,a.名前,b.男子名,b.女子名
from 親 a,(select c.code as code,
             c.seq as seq,
             c.名前 as 男子名,
             (select d.名前 as 女子名 from 女子 d
               where c.code=d.code
                 and c.seq=d.seq) as 女子名
             from 男子 c
             union select d.code as code,
             d.seq as seq,
             (select c.名前 from 男子 c
               where c.code=d.code
                 and c.seq=d.seq) as 男子名,
             d.名前 as 女子名
             from 女子 d) b
where a.code = b.code
order by a.code,b.seq;

--■■■full outer joinを使う方法■■■
select a.code,a.名前,b.男子名,b.女子名
from 親 a,(select coalesce(c.code,d.code) as code,
             coalesce(c.seq,d.seq) as seq,
             c.名前 as 男子名,
             d.名前 as 女子名
             from 男子 c full join 女子 d
             on(c.code = d.code and c.seq=d.seq)) b
where a.code = b.code
order by a.code,b.seq;

解説

完全外部結合を使用すれば、外部結合同士の和集合を取得できます。
oracle9iからは、full outer joinが使用できます。
full outer joinのouterは、省略可能です。

なお、coalesce(c.code,d.code)は、nvl(c.code,d.code)で代用可能です。

10-237 Oracle8iで、完全外部結合を模倣

---