Oracle 10gの新機能「Partitioned Outer Join」（外部結合で行の補完）

パーティション化された外部結合

　前バージョンのOracle 10gから新機能として導入されたPartitioned Outer Joinについて、基本事項から使用例まで、SQLのイメージを交えて解説します。

はじめに

　前バージョンのOracle 10gから新機能として導入されたPartitioned Outer Joinについて、基本事項から使用例まで、SQLのイメージを交えて解説します。

　Partitioned Outer Joinの便利さを実感しやすくするために、CodeZine Booksの『達人に学ぶ SQL徹底指南書』で扱われている問題をPartitioned Outer Joinを使って解くことも試みます。

対象読者

• OracleのPartitioned Outer Joinを使いたい方
• OracleのSQLの理解を深めたい方

必要な環境

　本稿で扱うSQLは、Oracle 11.2.0.1.0で動作確認しました。

1. Partitioned Outer Joinとは

　Partitioned Outer Joinは、Oracle 10g R1の新機能で、パーティションを切ってできた各集合と外部結合させることができます。Partitioned Outer Joinは、パーティション化された外部結合とも呼ばれます。

　Partitioned Outer Joinには、左外部結合（Left Outer Join）で使われるPartitioned Outer Joinと、右外部結合（Right Outer Join）で使われるPartitioned Outer Joinがありますが、本稿では前者のみを扱います。

2. Partitioned Outer Joinの基本的な使用例

　まずは、Partitioned Outer Joinの基本的な使用例について見てみます。サンプルを見てみましょう。

MasterTable

Key

10

20

30

40

TranTable

ID	Key	Name
AAAA	10	aaaa
AAAA	20	bbbb
BBBB	10	cccc
CCCC	10	dddd
CCCC	30	eeee

　TranTableでIDごとに、MasterTableに存在しないKeyを補完します。例えば、ID=AAAAの集合には、Key=30の行とKey=40の行が存在しませんので、Key=30の行とKey=40の行が補完されます。なお、補完された行のName列はNULLとします。

出力結果

ID	Key	Name
AAAA	10	aaaa
AAAA	20	bbbb
AAAA	30	null
AAAA	40	null
BBBB	10	cccc
BBBB	20	null
BBBB	30	null
BBBB	40	null
CCCC	10	dddd
CCCC	20	null
CCCC	30	eeee
CCCC	40	null

　このように、集合ごとにマスタ側に存在しなければ行を補完する、といった要件でPartitioned Outer Joinは特に効果を発揮します。Partitioned Outer Joinを使って上記の出力結果を取得するSQLを見てみましょう。

Partitioned Outer Joinを使ったSQL

select b.ID,a.Key,b.Name
  from MasterTable a
  Left Join TranTable b
  partition by (b.ID)
    on a.Key = b.Key
order by b.ID,a.Key;

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

　SQLのイメージは、下のようになります。partition by (b.ID)ですので、b.IDごとに区切る赤線をイメージし、左外部結合（Left Join）のベン図もイメージして、それぞれの左外部結合の結果がunion allされると理解すると分かりやすいでしょう。

SQLのイメージ

単なるLeft Joinとの違い

　なお、Partitioned Outer Joinではなく、下記のような単なるLeft Joinだと違う出力結果となります。

単なるLeft Join

select b.ID,a.Key,b.Name
  from MasterTable a
  Left Join TranTable b
    on a.Key = b.Key
order by b.ID,a.Key;

出力結果

ID	Key	Name
AAAA	10	aaaa
AAAA	20	bbbb
BBBB	10	cccc
CCCC	10	dddd
CCCC	30	eeee
null	40	null

Partitioned Outer Joinの代用法

　ちなみに、下記のSQLのようにクロスジョインと外部結合を組み合わせて、Partitioned Outer Joinと同じ結果を取得することもできますが、Partitioned Outer Joinを使ったほうがシンプルだと思います。

Partitioned Outer Joinの代用法

select a.ID,b.Key,c.Name
  from (select distinct ID from TranTable) a
cross Join (select Key from MasterTable) b
 Left Join TranTable c
   on a.ID  = c.ID
  and b.Key = c.Key
order by a.ID,b.Key;