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

SQLパズル

TableA      TableB
PKey  Val   PKey  Val
----  ---   ----  ---
1111  100   1111  900
1111  100   4444  800
2222  200   6666  700
3333  300
3333  300
4444  400

Oracle8iで、以下の完全外部結合の結果を取得する。

select a.PKey as aPKey,a.Val as aVal,
       b.PKey as bPKey,b.Val as bVal
  from TableA a full join TableB b
    on a.PKey = b.PKey;

出力結果
aPKey  aVal  bPKey  bVal
-----  ----  -----  ----
 1111   100   1111   900
 1111   100   1111   900
 4444   400   4444   800
 2222   200   null  null
 3333   300   null  null
 3333   300   null  null
 null  null   6666   700

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

データ作成スクリプト

create table TableA(PKey,Val) as
select 1111,100 from dual union all
select 1111,100 from dual union all
select 2222,200 from dual union all
select 3333,300 from dual union all
select 3333,300 from dual union all
select 4444,400 from dual;

create table TableB(PKey,Val) as
select 1111,900 from dual union all
select 4444,800 from dual union all
select 6666,700 from dual;

SQL

col aPKey for 9999
col bPKey for 9999

--■■■外部結合を2回使う方法■■■
select a.PKey as aPKey,a.Val as aVal,b.PKey as bPKey,b.Val as bVal
  from TableA a,TableB b
 where a.PKey = b.PKey(+)
union all
select a.PKey as aPKey,a.Val as aVal,b.PKey as bPKey,b.Val as bVal
  from TableA a,TableB b
 where a.PKey(+) = b.PKey
   and a.PKey is null;

--■■■not existsを使う方法■■■
select a.PKey as aPKey,a.Val as aVal,b.PKey as bPKey,b.Val as bVal
  from TableA a,TableB b
 where a.PKey = b.PKey(+)
union all
select null,null,b.PKey as bPKey,b.Val as bVal
  from TableB b
 where not exists(select 1 from TableA a
                   where a.PKey = b.PKey);

解説

下記は、unionによって、元々の集合の重複行を失うダメな例です。

select a.PKey as aPKey,a.Val as aVal,b.PKey as bPKey,b.Val as bVal
  from TableA a,TableB b
 where a.PKey = b.PKey(+)
union
select a.PKey as aPKey,a.Val as aVal,b.PKey as bPKey,b.Val as bVal
  from TableA a,TableB b
 where a.PKey(+) = b.PKey;

---