対象読者
- SQLでNOT INを使おうとしている方
- SQLにおけるNULLの仕様を知りたい方
NOT INを使ってみる
前提となるテーブル
グループA
| 名前 | 都道府県 |
|---|---|
| 山田 | 千葉 |
| 田中 | 東京 |
グループB
| 名前 | 都道府県 |
|---|---|
| アラン | 千葉 |
| イーサン | 東京 |
| ウィリアム | 北海道 |
| ベンジー | 東京 |
| ルーサー | 東京 |
SQLを実行してみる
グループBの行のうち、グループAに存在しない都道府県の値を持つ行を取得する
この場合、北海道はグループAに含まれないため、ウィリアムがSQLの結果として抽出されるはずです。
SELECT * FROM group_b
WHERE prefecture NOT IN (SELECT prefecture FROM group_a);
実行結果
| 名前 | 都道府県 |
|---|---|
| ウィリアム | 北海道 |
期待通りの結果が出力されました。特に問題ないように思えます。しかし、 データにNULLがある場合はどうなるでしょうか。
前提となるテーブル(NULLあり)
グループAに対して、新たに鈴木さんを追加します。ただし、鈴木さんの都道府県はNULLとします。
グループA
| 名前 | 都道府県 |
|---|---|
| 山田 | 千葉 |
| 田中 | 東京 |
| 鈴木 | NULL |
グループB
| 名前 | 都道府県 |
|---|---|
| アラン | 千葉 |
| イーサン | 東京 |
| ウィリアム | 北海道 |
| ベンジー | 東京 |
| ルーサー | 東京 |
SQLを実行してみる(NULLあり)
先ほどとまったく同じSQLを実行してみます。
SELECT * FROM group_b
WHERE prefecture NOT IN (SELECT prefecture FROM group_a);
実行結果
| 名前 | 都道府県 |
|---|---|
な、なんと0件！！！
先ほどのウィリアムが出力されなくなっていしまいました。
なぜNULLがあると出力されなくなったのか
それは、このSELECT文でWHERE句の評価が一度もtrueとならなかったためです。
SQLでは、WHERE句の評価結果がtrueになった行のみを処理対象とします。WHERE句がtrueにならなければSELECTの処理対象となりません。
なぜWHERE句がtrueにならないのか
雑な説明ですが考え方は以下の図の通りです。
グループAの都道府県列にNULLが1件でも存在する限り、WHERE句の評価結果は必ずunknownとなります。SQLを何度実行しようともWHERE句がtrueになることはないため、1行も処理対象として抽出されません。
覚えておくべきことは**「AND演算の中に1つでもunknownがあると、結果は絶対にtrueにならない」**ということです。NULLの仕様を理解しないと予期しない結果を出力するSQLを作ってしまうかもしれないので注意が必要です。
おまけ：解決策
- NOT EXISTSを使う
- NOT NULL制約を設ける
SELECT * FROM group_b bb
WHERE NOT EXISTS (SELECT * FROM group_a aa WHERE aa.prefecture = bb.prefecture);
解説は省略します。