4. Partitioned Outer Joinで関係除算
次は、『達人に学ぶ SQL徹底指南書』の129ページの「差集合で関係除算を表現する」をPartitioned Outer Join
を使って解いてみます。テーブルのデータと、出力結果は、下記となります。
Skill |
Oracle |
UNIX |
Java |
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
を使った答えとなります。
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
ごとに区切る赤線をイメージしています。
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だと判定しています。
最後に
パーティションを切ってできた各集合と外部結合させるPartitioned Outer Join
について解説しました。機会があったら使ってみるといいでしょう。
参考資料
- 『外部結合』
Oracleの公式マニュアルの
Left Outer Join
やPartitioned Outer Join
に関する説明です。 - 『OracleSQLパズル 3-34 Partitioned Outer Join』
Partitioned Outer Join
のサンプルを置いてます。