Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

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

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2010/05/19 14:00

ダウンロード SourceCode (1.0 KB)

 前バージョンの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のイメージ
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;

  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

All contents copyright © 2005-2019 Shoeisha Co., Ltd. All rights reserved. ver.1.5