9-9 差集合が空集合かチェック

SQLパズル

工場テーブル    操縦士テーブル
飛行機名        操縦士ID   操縦可能機
--------        --------  ----------
ジャビィ               1    ジャビィ
ハンター               2    ファルコ
ファルコ               3    ハンター
                       4    ジャビィ
                       4    ファルコ
                       5    ジャビィ
                       5    ハンター
                       5    ファルコ
                       5    ペリカン
                       6    ハンター
                       6    ペリカン
                       7    ジャビィ
                       7    ファルコ
                       7    ペリカン
                       8    ジャビィ
                       8    ハンター
                       8    ファルコ

工場テーブルにある全ての飛行機を操縦できる操縦士IDと、操縦可能機を出力する。

出力結果
操縦士ID   飛行機名
--------  ----------
       5    ジャビィ
       5    ハンター
       5    ファルコ
       5    ペリカン
       8    ジャビィ
       8    ハンター
       8    ファルコ

こちらを参考にさせていただきました(英語)
プログラマのためのSQL第2版の19章[データの分割]を参考にさせていただきました
SQLパズル 第2版のパズル21 [飛行機と飛行士] を参考にさせていただきました

データ作成スクリプト

create table 工場(飛行機名 char(8));

create table 操縦士(
操縦士ID   number(1),
操縦可能機 char(8));

--テストパターン1 正常パターン
delete from 工場;
delete from 操縦士;
insert all
into 工場 values('ジャビィ')
into 工場 values('ハンター')
into 工場 values('ファルコ')
into 操縦士 values(1,'ジャビィ')
into 操縦士 values(2,'ファルコ')
into 操縦士 values(3,'ハンター')
into 操縦士 values(4,'ジャビィ')
into 操縦士 values(4,'ファルコ')
into 操縦士 values(5,'ジャビィ')
into 操縦士 values(5,'ハンター')
into 操縦士 values(5,'ファルコ')
into 操縦士 values(5,'ペリカン')
into 操縦士 values(6,'ハンター')
into 操縦士 values(6,'ペリカン')
into 操縦士 values(7,'ジャビィ')
into 操縦士 values(7,'ファルコ')
into 操縦士 values(7,'ペリカン')
into 操縦士 values(8,'ジャビィ')
into 操縦士 values(8,'ハンター')
into 操縦士 values(8,'ファルコ')
select 1 from dual;
commit;

--テストパターン2 特殊パターン
delete from 工場;
delete from 操縦士;
insert all
into 工場 values('ラップル')
into 操縦士 values(1,'ジャビィ')
into 操縦士 values(2,'ファルコ')
into 操縦士 values(3,'ハンター')
into 操縦士 values(4,'ジャビィ')
into 操縦士 values(4,'ファルコ')
into 操縦士 values(5,'ジャビィ')
into 操縦士 values(5,'ハンター')
into 操縦士 values(5,'ファルコ')
into 操縦士 values(5,'ペリカン')
into 操縦士 values(6,'ハンター')
into 操縦士 values(6,'ペリカン')
into 操縦士 values(7,'ジャビィ')
into 操縦士 values(7,'ファルコ')
into 操縦士 values(7,'ペリカン')
into 操縦士 values(8,'ジャビィ')
into 操縦士 values(8,'ハンター')
into 操縦士 values(8,'ファルコ')
select 1 from dual;
commit;

--テストパターン3 空集合
delete from 工場;
delete from 操縦士;
insert all
into 操縦士 values(1,'ジャビィ')
into 操縦士 values(2,'ファルコ')
into 操縦士 values(3,'ハンター')
into 操縦士 values(4,'ジャビィ')
into 操縦士 values(4,'ファルコ')
into 操縦士 values(5,'ジャビィ')
into 操縦士 values(5,'ハンター')
into 操縦士 values(5,'ファルコ')
into 操縦士 values(5,'ペリカン')
into 操縦士 values(6,'ハンター')
into 操縦士 values(6,'ペリカン')
into 操縦士 values(7,'ジャビィ')
into 操縦士 values(7,'ファルコ')
into 操縦士 values(7,'ペリカン')
into 操縦士 values(8,'ジャビィ')
into 操縦士 values(8,'ハンター')
into 操縦士 values(8,'ファルコ')
select 1 from dual;
commit;

SQL

--■■■差集合が空集合かチェックする方法■■■
select 操縦士ID,操縦可能機 as 飛行機名
from 操縦士 a
where not exists(select b.飛行機名 from 工場 b
                  minus
                 select b.操縦可能機 from 操縦士 b
                  where b.操縦士ID=a.操縦士ID);

--■■■外部結合と分析関数を使う方法■■■
select 操縦士ID,操縦可能機
from (select distinct a.操縦士ID,a.操縦可能機,
      count(decode(b.飛行機名,a.操縦可能機,1)) over(partition by a.操縦士ID) as hasCount,
      nvl(b.needCount,0) as needCount
        from 操縦士 a Left Join (select 飛行機名,
                                 count(*) over() as needCount
                                   from 工場) b
                        on 1=1)
 where hasCount = needCount
order by 操縦士ID,操縦可能機;

解説

操縦士IDごとに、工場との差集合が空集合かチェックするのが
一番分かりやすいと思います。

5-29 having句でスカラー問い合わせ
CodeZine：分析関数の衝撃（完結編）

---