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