可視化 SQL | (厳密な) 関係除算 について

初めに

この問題は、SQLパズル #21 『飛行機と飛行士』 を参考にしています
手元に『SQL パズル』があれば問題の詳細が記載されているのでよりわかりやすいです

この記事では、(厳密な) 関係除算について詳しく見ていきたいと思います

この(厳密な)・・・の意味ですが
パイロットが操縦できる飛行機の数と、格納庫に有る飛行機の数が完全に一致している
という事です

『関係除算を詳しく解説してみる』で解説した時の回答は Smith と Wilson の
2名のパイロットが抽出できましたが、これは厳密には違っています
　⇒　Wilson は 操縦出来る飛行機が1機多い為です

このクイズでは、Smith さんだけを取り出せるSQLを考えます

PostgreSQL で動作確認しています

テーブル と データ

SQL

CREATE TABLE PilotSkills (
	pilot CHAR(15) NOT NULL,
	plane CHAR(15) NOT NULL,
	PRIMARY KEY (pilot, plane)
);

CREATE TABLE Hangar (
	plane CHAR(15) PRIMARY KEY
);

INSERT INTO PilotSkills
VALUES ('Celko',   'Piper Cub'),
       ('Higgins', 'B-52 Bomber'),
       ('Higgins', 'F-14 Fighter'),
       ('Higgins', 'Piper Cub'),
       ('Jones',   'B-52 Bomber'),
       ('Jones',   'F-14 Bomber'),
       ('Smith',   'B-1 Bomber'),
       ('Smith',   'B-52 Bomber'),
       ('Smith',   'F-14 Fighter'),
       ('Wilson',  'B-1 Bomber'),
       ('Wilson',  'B-52 Bomber'),
       ('Wilson',  'F-14 Fighter'),
       ('Wilson',  'F-17 Fighter');

INSERT INTO Hangar
VALUES ('B-1 Bomber'),
       ('B-52 Bomber'),
       ('F-14 Fighter');

▼ 出力

Hangar テーブルには3機の飛行機、これと同じ飛行機を操縦できる飛行機が3機有る
Smith さんだけを取出します

元データ を LEFT JOIN で作る

SQL

SELECT PS1.*, H1.*
FROM PilotSkills AS PS1 
  LEFT JOIN Hangar AS H1 ON PS1.plane = H1.plane

▼ 出力

飛行機の数をカウント①

SQL

SELECT 
   PS1.pilot,
   array_agg(PS1.plane), --操縦出来る飛行機
   COUNT(PS1.plane),     --操縦出来る飛行機の数
   (SELECT array_agg(plane) FROM Hangar), --格納庫に有る飛行機
   (SELECT COUNT(plane) FROM Hangar)      --格納庫に有る飛行機の数
FROM PilotSkills AS PS1 
 LEFT JOIN Hangar AS H1 ON PS1.plane = H1.plane
GROUP BY PS1.pilot

▼ 出力

パイロットが運転できる飛行機一覧とその数
格納庫に有る飛行機一覧とその数

飛行機の数が同じ行を取出す①

SQL

SELECT 
   PS1.pilot,
   array_agg(PS1.plane), --操縦出来る飛行機
   COUNT(PS1.plane),     --操縦出来る飛行機の数
   (SELECT array_agg(plane) FROM Hangar), --格納庫に有る飛行機
   (SELECT COUNT(plane) FROM Hangar)      --格納庫に有る飛行機の数
FROM PilotSkills AS PS1 
 LEFT JOIN Hangar AS H1 ON PS1.plane = H1.plane
GROUP BY PS1.pilot

HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)

▼ 出力

下記条件を追加
　⇒　HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)

操縦出来る飛行機の数と格納庫に有る飛行機の数が同じ行を取出す
　⇒　どちらも 3 になる行

しかし、これでは不十分。 Higgins は格納庫にある全ての飛行機が操縦できない
ただ、操縦できる飛行機の数と、格納庫に有る飛行機の数が一致しているだけ

飛行機の数をカウント②

SQL

SELECT 
   PS1.pilot,
   array_agg(PS1.plane), --操縦出来る飛行機
   COUNT(PS1.plane),     --操縦出来る飛行機の数
   (SELECT array_agg(plane) FROM Hangar), --格納庫に有る飛行機
   (SELECT COUNT(plane) FROM Hangar),     --格納庫に有る飛行機の数
   array_agg(H1.plane),  --操縦出来る,格納庫に有る飛行機
   COUNT(H1.plane)       --操縦出来る,格納庫に有る飛行機の数
FROM PilotSkills AS PS1 
 LEFT JOIN Hangar AS H1 ON PS1.plane = H1.plane
GROUP BY PS1.pilot

▼ 出力

パイロットが操縦出来る格納庫に有る飛行機の一覧とその数をSELECT文に追加した
　⇒　array_agg(H1.plane)
　⇒　COUNT(H1.plane)

この飛行機の情報は PilotsKills テーブルに対して LEFT JOIN しているので
パイロットが操縦出来る、格納庫に有る飛行機・・・と見る事が出来る
　⇒　下記画像の右端

この飛行機の数と、格納庫に有る飛行機の数が一致する行を見つける

飛行機の数が同じ行を取出す②

SQL

SELECT 
   PS1.pilot,
   array_agg(PS1.plane), --操縦出来る飛行機
   COUNT(PS1.plane),     --操縦出来る飛行機の数
   (SELECT array_agg(plane) FROM Hangar), --格納庫に有る飛行機
   (SELECT COUNT(plane) FROM Hangar),     --格納庫に有る飛行機の数
   array_agg(H1.plane),  --操縦出来る,格納庫に有る飛行機
   COUNT(H1.plane)       --操縦出来る,格納庫に有る飛行機の数
FROM PilotSkills AS PS1 
 LEFT JOIN Hangar AS H1 ON PS1.plane = H1.plane
GROUP BY PS1.pilot

HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)
 AND COUNT(H1.plane)  = (SELECT COUNT(plane) FROM Hangar)

▼ 出力

下記条件をHAVING に追加する
　⇒　COUNT(H1.plane) = (SELECT COUNT(plane) FROM Hangar)
　⇒　Smith さんだけが残る

回答SQL

SQL

SELECT PS1.pilot
FROM PilotSkills AS PS1 
LEFT JOIN Hangar AS H1 ON PS1.plane = H1.plane
GROUP BY PS1.pilot
HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)
   AND COUNT(H1.plane)  = (SELECT COUNT(plane) FROM Hangar)

▼ 出力

pilot
Smith

参考文献

SQLパズル 第2版~プログラミングが変わる書き方/考え方 | Joe Celko, ミック