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

はじめに

　前バージョンの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の基本的な使用例について見てみます。サンプルを見てみましょう。

　TranTableでIDごとに、MasterTableに存在しないKeyを補完します。例えば、ID=AAAAの集合には、Key=30の行とKey=40の行が存在しませんので、Key=30の行とKey=40の行が補完されます。なお、補完された行のName列は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されると理解すると分かりやすいでしょう。

SQLのイメージ

単なる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;

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;