--■■■union allを使う方法■■■
select PKey,Val as base,to_char(null) as adv from BaseTable
union all
select b.PKey,a.Val,b.Val
from BaseTable a,AdvTable b
where a.PKey=b.PKey
order by PKey,base,adv;
--■■■テーブルにIDを付与する方法■■■
select PKey,base,adv
from (select ID,PKey,
max(decode(ID,1,Val)) over(partition by PKey) as base,
decode(ID,2,Val) as adv,
max(case when ID=1 then 1 else 0 end) over(partition by PKey) as willOut
from (select 1 as ID,PKey,Val from BaseTable union all
select 2 ,PKey,Val from AdvTable))
where willOut=1
order by ID,PKey;
--■■■Outer unionもどきを使う方法■■■
select PKey,base,adv
from (select PKey,max(base) over(partition by PKey) as base,adv,
count(base) over(partition by PKey) as willOut
from (select PKey,Val as base,to_char(null) as adv from BaseTable union all
select PKey,to_char(null),Val from AdvTable))
where willOut=1
order by adv nulls first,PKey;