特集記事

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

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

山岸賢治[著]

2010/05/19 14:00

ポスト

ダウンロード 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.empとhaving count(*) = count(b.Skill)によって、b.empごとに左外部結合によってb.Skillがnullになった行が0行かを判定しています。

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

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.cntとhaving count(*) = a.cntによって、empごとの件数=Skillsテーブルの行数であれば、Skillsテーブルの全てのSkillを持つempだと判定しています。