はじめに
前バージョンの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の基本的な使用例について見てみます。サンプルを見てみましょう。
| Key |
| 10 |
| 20 |
| 30 |
| 40 |
| 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を見てみましょう。
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されると理解すると分かりやすいでしょう。
単なるLeft Joinとの違い
なお、Partitioned Outer 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を使ったほうがシンプルだと思います。
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;
