SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

特集記事

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

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード SourceCode (1.0 KB)

3. Partitioned Anti Joinもどき

 次は、『達人に学ぶ SQL徹底指南書』の145ページの「テーブルに存在しないデータを探す」をPartitioned Outer Joinを使って解いてみます。テーブルのデータと、出力結果は、下記となります。

Meetings
meeting person
第1回 伊藤
第1回 水島
第1回 坂東
第2回 伊藤
第2回 宮田
第3回 坂東
第3回 水島
第3回 宮田

 それぞれのmeetingに出席しなかったpersonを求めます。

出力結果
meeting person
第1回 宮田
第2回 坂東
第2回 水島
第3回 伊藤

 下記が、Partitioned Outer Joinを使った答えとなります。

Partitioned Outer Joinを使う方法
select b.meeting,a.person
  from (select distinct person from Meetings) a
  Left Join Meetings b partition by(b.meeting)
    on a.person = b.person
 where b.person is null
order by b.meeting;

 解説すると、まず、(select distinct person from Meetings)によって、personの一覧を作成します。次に、partition by(b.meeting)を指定したPartitioned Outer Joinによって、b.meetingでパーティションを切ってできた集合ごとに左外部結合を行い、where b.person is nullによって、左外部結合の結果がnullになった行のみを出力しています。Partitioned Anti Joinもどきと考えると分かりやすいと思います。

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

SQLのイメージ
SQLのイメージ

Partitioned Outer Joinの代用法

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

Partitioned Outer Joinの代用法
select a.meeting,b.person
  from (select distinct meeting from Meetings) a,
       (select distinct person  from Meetings) b
minus
select meeting,person
  from Meetings
order by meeting;

次のページ
4. Partitioned Outer Joinで関係除算

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
特集記事連載記事一覧

もっと読む

この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/5139 2010/05/19 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング