SHOEISHA iD

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

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

特集記事

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

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

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

ダウンロード SourceCode (1.0 KB)

4. Partitioned Outer Joinで関係除算

 次は、『達人に学ぶ SQL徹底指南書』の129ページの「差集合で関係除算を表現する」をPartitioned Outer Joinを使って解いてみます。テーブルのデータと、出力結果は、下記となります。

Skills
Skill
Oracle
UNIX
Java
EmpSkills
emp Skill
相田 Oracle
相田 UNIX
相田 Java
相田 C#
神崎 Oracle
神崎 UNIX
神崎 Java
平井 UNIX
平井 Oracle
平井 PHP
平井 Perl
平井 C++
若田 Perl
渡来 Oracle

 EmpSkillsテーブルのemp列ごとでSkillsテーブルのSkillを全て持つempを出力します。なお、Skillsテーブルが空集合のケースは考えません。

出力結果
emp
神崎
相田

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

Partitioned Outer Joinを使う方法
select b.emp
  from Skills a
  Left Join EmpSkills b partition by(b.emp)
    on a.Skill = b.Skill
group by b.emp
having count(*) = count(b.Skill);

 解説すると、Skillsテーブルと、partition by(b.emp)を指定したPartitioned Outer Joinによって、b.empでパーティションを切ってできた集合ごとに左外部結合を行い、group by b.emphaving count(*) = count(b.Skill)によって、b.empごとに左外部結合によってb.Skillがnullになった行が0行かを判定しています。

 SQLのイメージは、下のようになります。partition by(b.emp)ですので、b.empごとに区切る赤線をイメージしています。

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

Partitioned Outer Joinを使わない方法

 下記のようにPartitioned Outer Joinを使わない方法もあります。

Partitioned Outer Joinを使わない方法
select b.emp
  from (select Skill,count(*) over() as cnt from Skills) a
  Join EmpSkills b
    on a.Skill = b.Skill
group by b.emp,a.cnt
having count(*) = a.cnt;

 解説すると、事前にインラインビューでSkillsテーブルの行数を求めて、Skillが等しいことを条件として内部結合し、group by b.emp,a.cnthaving count(*) = a.cntによって、empごとの件数=Skillsテーブルの行数であれば、Skillsテーブルの全てのSkillを持つempだと判定しています。

最後に

 パーティションを切ってできた各集合と外部結合させるPartitioned Outer Joinについて解説しました。機会があったら使ってみるといいでしょう。

参考資料

  1. 外部結合
    Oracleの公式マニュアルのLeft Outer JoinPartitioned Outer Joinに関する説明です。
  2. OracleSQLパズル 3-34 Partitioned Outer Join
    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」など、さまざまなカンファレンスを企画・運営しています。

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

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

メールバックナンバー

アクセスランキング

アクセスランキング