関係除算を表現する
次に関係除算を表現するSQLについて考えます。「SQLで集合演算」では、以下のSQLが提示されています。
SELECT DISTINCT emp FROM EmpSkills ES1 WHERE NOT EXISTS (SELECT skill FROM Skills minus SELECT skill FROM EmpSkills ES2 WHERE ES1.emp = ES2.emp);
これを分析関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。
Skill |
Oracle |
UNIX |
Java |
Emp | Skill |
相田 | Oracle |
相田 | UNIX |
相田 | Java |
相田 | C# |
神崎 | Oracle |
神崎 | UNIX |
神崎 | Java |
平井 | UNIX |
平井 | Oracle |
平井 | PHP |
平井 | Perl |
平井 | C++ |
若田部 | Perl |
渡来 | Oracle |
Emp |
相田 |
神崎 |
手続き型の言語であれば、次のような手順となるでしょう。
- SkillsテーブルのSkillを配列に保存し、それぞれにフラグを用意
- EmpSkillsテーブルをEmpの昇順にソート
- 配列に保存したSkillsテーブルのSkillのフラグを初期化
- EmpSkillsテーブルをEmpの最小値からループ開始
- 配列に保存したSkillsテーブルのSkillにフラグをつける
- EmpSkillsテーブルのEmpがブレイクしたら、3に戻る
分析関数を使ったSQLでも似たような考え方を使います。
select a.Emp from EmpSkills a,(select Skill, count(*) over() as SkillCount from Skills) b where a.Skill = b.Skill group by a.Emp,b.SkillCount having count(*) = b.SkillCount;
where
句で内部結合して、group by
でグループ化を行った後のイメージは、次のようになります。
この後、having
句の条件としてcount(*) = b.SkillCount
を指定しています。必要なスキルの数を示すb.SkillCount
は全て3
ですから、相田さんと神埼さんのみが返されます。ただし、Skillsテーブルが空集合の場合、上記のSQLではデータが出力されません。これを考慮したSQLは次のようになります。
select a.Emp from EmpSkills a Left Join (select Skill, count(*) over() as needCount from Skills) b on 1=1 group by a.Emp,b.needCount having count(decode(a.Skill,b.Skill,1)) = nvl(b.needCount,0);
Left Join
句で外部結合して、group by
でグループ化を行った後のイメージは、次のようになります(27行目以降は割愛)。
having
句では、decode
関数を使ってa.Skill
とb.Skill
が一致するレコードの数と、Skills
テーブルのレコードの数が等しいか判定しています。これは、count
関数がnull
をカウント対象としないことへの考慮です。
外部結合の結合条件には1=1
を指定し、判定結果が必ずtrue
となるように記述しています。Skillsテーブルが空集合でない場合は、全ての行と結合した結合結果(クロスジョインと同じ結合結果)が返されます。また、Skillsテーブルが空集合の場合は、結合先の情報をnullとした結合結果が返される仕組みです。
Skillsテーブルが空集合の場合は、having
句の条件式の左辺のcount(decode(a.Skill,b.Skill,1))
は0
、右辺のnvl(b.needCount,0)
も0
となるため、having
句において等号が成立し、EmpSkillsテーブルの全てのEmp
が出力されます。
Oracle10gの新機能「Partitioned Outer Join」を使用した別解(Skillsテーブルが空集合の場合に非対応)も記載しておきます。
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);