10-109 ANSI構文でクロスジョイン

SQLパズル

tabテーブル
Code  Seq  Data
----  ---  ----
 001    1  a
 001    2  b
 001    3  c
 002    1  d
 002    3  e
 003    3  f
 004    2  g

ひとつのcodeに対して、必ず3レコード存在するように
以下の出力をする。

出力結果
Code  Seq  Data
----  ---  ----
 001    1  a
 001    2  b
 001    3  c
 002    1  d
 002    2  null
 002    3  e
 003    1  null
 003    2  null
 003    3  f
 004    1  null
 004    2  g
 004    3  null

データ作成スクリプト

create table tab(
code char(3),
seq  number(1),
data char(1));

insert into tab values('001',1,'a');
insert into tab values('001',2,'b');
insert into tab values('001',3,'c');
insert into tab values('002',1,'d');
insert into tab values('002',3,'e');
insert into tab values('003',3,'f');
insert into tab values('004',2,'g');
commit;

SQL

col data for a4

--■■■ANSI構文を使う方法■■■
select a.code,b.seq,c.data
from
(select distinct code from tab) a
cross join
(select 1 as seq from dual
union all select 2 from dual
union all select 3 from dual) b
Left Join tab c
on  a.code = c.code
and b.seq =  c.seq
order by a.code,b.seq;

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select b.Code,a.Seq,b.Data
  from (select 1 as seq from dual
   union all select 2 from dual
   union all select 3 from dual) a
  Left Join tab b
partition by (b.Code)
    on (a.Seq=b.Seq)
order by b.Code,a.Seq;

--■■■model句を使う方法(10g以降)■■■
select code,seq,data
  from tab
 model
 partition by (code)
 dimension by (seq)
 measures(data)
 rules(data[1] = data[1],
       data[2] = data[2],
       data[3] = data[3])
order by code,seq;

解説

ANSI構文でクロスジョインさせるか、
10g以降ならPartitioned Outer Joinやmodel句を使ってもいいでしょう。

3-34 Partitioned Outer Join
10-272 model句でPartitioned Outer Joinもどき

---