3. Partitioned Anti Joinもどき
次は、『達人に学ぶ SQL徹底指南書』の145ページの「テーブルに存在しないデータを探す」をPartitioned Outer Join
を使って解いてみます。テーブルのデータと、出力結果は、下記となります。
meeting | person |
第1回 | 伊藤 |
第1回 | 水島 |
第1回 | 坂東 |
第2回 | 伊藤 |
第2回 | 宮田 |
第3回 | 坂東 |
第3回 | 水島 |
第3回 | 宮田 |
それぞれのmeetingに出席しなかったpersonを求めます。
meeting | person |
第1回 | 宮田 |
第2回 | 坂東 |
第2回 | 水島 |
第3回 | 伊藤 |
下記が、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
されると理解すると分かりやすいと思います。
Partitioned Outer Joinの代用法
ちなみに、下記のSQLのようにクロスジョインとminus
を組み合わせて、上記のSQLと同じ結果を取得することもできますが、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;