3-34 Partitioned Outer Join

SQLパズル

MainTable    SubTable
ID  FKey     FKey  Val    PartitionID
--  ----     ----  -----  -----------
 1     1        1  AAAAA            1
 2     2        2  BBBBB            1
 3     3        3  CCCCC            2
 4     9        7  DDDDD            3
                7  EEEEE            3
                2  FFFFF            4
                3  GGGGG            4
                1  HHHHH            5
                1  IIIII            5

Partitioned Outer Join(パーティション化された外部結合)
について考察します。

データ作成スクリプト

create table MainTable as(
select 1 as ID,1 as FKey from dual union
select 2,2 from dual union
select 3,3 from dual union
select 4,9 from dual);

create table SubTable as(
select 1 as FKey,'AAAAA' as Val,1 as PartitionID from dual union
select 2,'BBBBB',1 from dual union
select 3,'CCCCC',2 from dual union
select 7,'DDDDD',3 from dual union
select 7,'EEEEE',3 from dual union
select 2,'FFFFF',4 from dual union
select 3,'GGGGG',4 from dual union
select 1,'HHHHH',5 from dual union
select 1,'IIIII',5 from dual);

SQL

--■■■ただの外部結合■■■
select a.ID,a.FKey,b.FKey,b.Val,b.PartitionID
  from MainTable a
  Left outer join SubTable b
    on (a.FKey = b.FKey)
order by a.ID;

ID  FKey  FKey  Val    PartitionID
--  ----  ----  -----  -----------
 1     1     1  AAAAA            1
 1     1     1  HHHHH            5
 1     1     1  IIIII            5
 2     2     2  BBBBB            1
 2     2     2  FFFFF            4
 3     3     3  CCCCC            2
 3     3     3  GGGGG            4
 4     9  null   null         null

--■■■Partitioned Outer Join■■■
select a.ID,a.FKey,b.FKey,b.Val,b.PartitionID
  from MainTable a
  Left outer join SubTable b
  partition by (b.PartitionID)
    on (a.FKey = b.FKey)
order by a.ID,b.PartitionID;

ID  FKey  FKey  Val    PartitionID
--  ----  ----  -----  -----------
 1     1     1  AAAAA            1
 1     1  null  null             2
 1     1  null  null             3
 1     1  null  null             4
 1     1     1  HHHHH            5
 1     1     1  IIIII            5
 2     2     2  BBBBB            1
 2     2  null  null             2
 2     2  null  null             3
 2     2     2  FFFFF            4
 2     2  null  null             5
 3     3  null  null             1
 3     3     3  CCCCC            2
 3     3  null  null             3
 3     3     3  GGGGG            4
 3     3  null  null             5
 4     9  null  null             1
 4     9  null  null             2
 4     9  null  null             3
 4     9  null  null             4
 4     9  null  null             5

解説

Oracle10g以降から使用できる、
Partitioned Outer Joinを使うと、
パーティションを切ってできた、それぞれの集合と外部結合させることができます。

Leftを残す形で、
Partitioned(パーテションを切られた)
Outer(外部の集合)を
Join (結合)させる。
と覚えるといいでしょう。

分析関数とmodel句 --- Partitioned Outer Joinの脳内のイメージ

7-70 クロスジョインして分析関数
7-75 全ての年月の値を出力
10-109 ANSI構文でクロスジョイン
10-170 クロスジョインで組み合わせを列挙
10-319 Partitioned Outer Joinで全体の最小から最大まで補完

---

マニュアル(partitioned outer join)(英語)
マニュアル(パーティション化された外部結合)

US-OTNのスレッド(英語)

---