Qiita投稿 #29
今回は・・・
「達人に学ぶSQL徹底指南書 第２版」で解説されている二重否定クエリがぱっと見理解できなかったので、その時の備忘録です。
テーブルと取得内容の定義
テーブルは以下の通りです。
テーブル名：Teams
| member | team_id | status |
|---|---|---|
| ジョー | 1 | 待機 |
| ケン | 1 | 出動中 |
| ミック | 1 | 待機 |
| カレン | 2 | 出動中 |
| キース | 2 | 休暇 |
| ジャン | 3 | 待機 |
| ハート | 3 | 待機 |
| ディック | 3 | 待機 |
| ベス | 4 | 待機 |
| アレン | 5 | 出動中 |
| ロバート | 5 | 休暇 |
| ケーガン | 5 | 待機 |
取得内容：この中からチームの全員が待機中のメンバーを取得します。
SELECT team_id, member
FROM Teams T1
WHERE NOT EXISTS (SELECT *
FROM Teams T2
WHERE T1.team_id = T2.team_id
AND status <> '待機');
クエリ実行結果：
| team_id | member |
|---|---|
| 3 | ジャン |
| 3 | ハート |
| 3 | ディック |
| 4 | ベス |
解説
このクエリを言語化すると・・・
「チームの中で待機中ではないメンバーが存在しない」 です。
日本語でもややこしい
もう少しクエリ文寄りに言語化すると・・・
「チームが同じ且つ、ステータスが待機という条件の返り値がないチームを取得する」ですかね。
WHERE句の中がややこしいので、こちらにフォーカスして解読していきます。
WHERE NOT EXISTS
(SELECT *
FROM Teams T2
WHERE T1.team_id = T2.team_id
AND status <> '待機');
- 1人でも待機以外だとデータが取得できるのでNOT EXISTSで弾かれます。よって全員が待機だと１行も取得できずNOT EXISTSで条件と合致します。
-
WHERE T1.team_id = T2.team_idはテーブルを自己結合し、チームをグループ化していますので、GROUP BY team_idと同じ意味です。 -
status <> '待機'で「待機ではない」という比較をしています。 - チーム1とチーム2、チーム5は3.の「待機ではない」に当てはまるメンバーがいるので行が取得されます。
- チーム3とチーム4は「待機ではない」に全て当てはまらないので行が取得できません。
- したがって取得できなかったチームがNOT EXISTSでtrueとなり上記のチームが絞り込まれ、取得されます。
複雑なクエリでもFROM->WHERE->GROUP BY->HAVING->ORDER BY->SELECTの順で一つずつ処理していくと混乱せず実行できそうです。
サブクエリも混同させず、1つのクエリとして分解すれば徐々にストレスなく読み解けそうです。