野球大会から学ぶ MySQLのNULLの落とし穴

この記事で紹介したいこと

• MySQLの比較条件でのNULLの扱われ方
• NULLの扱い方
• SQLを書くときに気をつけていること

MySQLの比較条件でのNULLの扱われ方

ここは球太くんのチームの野球道具が管理されている倉庫
用具を登録しただけだと、どうやらstockカラムの値はNULLになるようです。

倉庫のデータ
baseball_stockテーブル

ID	name	stock
1	バット(軟式)	5
2	バット(硬式)	3
3	ボール(軟式)	10
4	ボール(硬式)	1
5	グローブ(軟式)	18
6	グローブ(硬式)	20
7	ロジン	NULL

球太くん「あ、今日野球観戦に行くんだった！念のためボール(硬式)を持っていこう」
球太くん「サインもらえるといいな〜」

ボール(硬式)を1個出庫

UPDATE `baseball_stock` SET `stock` = `stock` - 1 WHERE `name` = 'ボール(硬式)';

無事に1個出庫されました！
baseball_stockテーブル

ID	name	stock
1	バット(軟式)	5
2	バット(硬式)	3
3	ボール(軟式)	10
4	ボール(硬式)	0
5	グローブ(軟式)	18
6	グローブ(硬式)	20
7	ロジン	NULL

球太くんは無事にサインボールがもらえたようです。
よかったですね！

〜後日〜
球太くん「そうだ、来季の野球大会に向けて、足りない用具を補充しておかなきゃ」

在庫がない用具はどれかな〜

SELECT * FROM baseball_stock WHERE stock = 0;

結果

ID	name	stock
4	ボール(硬式)	0

球太くん「ボール(硬式)だけ補充すればいいのかな。」
球太くん「念のため他の用具の在庫数も見ておこう！」

在庫0以外の用具も見るぞ〜

SELECT * FROM baseball_stock WHERE stock <> 0;

結果

ID	name	stock
1	バット(軟式)	5
2	バット(硬式)	3
3	ボール(軟式)	10
5	グローブ(軟式)	18
6	グローブ(硬式)	20

球太くん「他の用具は補充しなくてもよさそうだな。よし！」

これで来季も楽しく野球ができますね！
しかしこの後、球太くんに大変なことが起こります。

〜野球大会当日〜
監督「今日はピッチャーのコントロールが悪いな・・・手が滑ってばっかりだ・・・」
監督「あれ、ピッチャーロジン持って無いじゃん」
監督「球太くん、君の作った在庫管理システム化はどうなっているんだい？」
球太くん「確かに不足している用具と、その他の在庫数も見たんですけどね・・・」
球太くん「見落としがあったのかもしれません・・・」

※ロジンはピッチャーが指につける滑り止めです

結果ピッチャーはコントロールが荒れに荒れて、初戦で負けてしまいました。
球速は速かったので、ロジンがあれば勝てていたかもしれませんね。

対処法

何故球太くんは

在庫をしっかり確認するSQL

SELECT * FROM baseball_stock WHERE stock = 0;
SELECT * FROM baseball_stock WHERE stock <> 0;

を実行したにもかかわらず、当日ロジンを用意できることができなかったのでしょうか。
それは、ロジンの在庫数の値がNULLであることが原因でした。
実はNULLは、比較演算子=にも<>にもヒットしません。

IS NULLやIS NOT NULL
を使う必要がありました。

球太くんに上記の知識を与えた上で、少し時を戻しましょう。

〜時を戻した後日〜
球太くん「そうだ、来季の野球大会に向けて、足りない用具を補充しておかなきゃ」

在庫がない用具はどれかな〜

SELECT * FROM baseball_stock WHERE stock = 0;

結果

ID	name	stock
4	ボール(硬式)	0

球太くん「おっといけない！NULLの考慮が漏れていた！」

在庫がない(NULL込み)用具はどれかな〜

SELECT * FROM baseball_stock WHERE stock = 0 OR stock IS NULL;

結果

ID	name	stock
4	ボール(硬式)	0
7	ロジン	NULL

球太くん「ボール(硬式)とロジンを補充・・・っと」

〜野球大会当日〜
監督「今日はピッチャーのコントロールがいいな！ロジンもちゃんと用意されている！」
監督「球太くんの在庫管理は完璧だ！」
球太くん「ありがとうございます！」

無事に野球大会で優勝することができました！
裏方の仕事も楽じゃないですね。

今回球太くんはIS NULLを使って、ロジンを確保できました。
記法の話しになりますが、他にもNULLを見つける手立てはあります。

結果は一緒だよ

SELECT * FROM baseball_stock WHERE stock = 0 OR stock IS NULL;
SELECT * FROM baseball_stock WHERE IFNULL(stock,0) = 0;
SELECT * FROM baseball_stock WHERE stock = 0 OR stock <=> NULL;

SQLを書くときに気をつけていること

NULL許可されているカラムの検索では、NULLのことを意識しましょう。
まだ=の検索はそれだけを見つけるには十分なのですが、
!=の検索には注意が必要です。
それ以外を見つけたいはずなのですが、NULLのレコードが検索対象外になってしまいます。
球太くんの例で言うとこちらです。

NULLのレコードが除外されてしまいます。

0以外の用具の在庫数も見るぞ〜

SELECT * FROM baseball_stock WHERE stock <> 0;

結果

ID	name	stock
1	バット(軟式)	5
2	バット(硬式)	3
3	ボール(軟式)	10
5	グローブ(軟式)	18
6	グローブ(硬式)	20

NULLのレコードを除外しない書き方

0以外の用具の在庫数も見るぞ〜

SELECT * FROM baseball_stock WHERE stock <> 0 OR stock IS NULL;
SELECT * FROM baseball_stock WHERE stock <> 0 OR stock <=> NULL;

結果

ID	name	stock
1	バット(軟式)	5
2	バット(硬式)	3
3	ボール(軟式)	10
5	グローブ(軟式)	18
6	グローブ(硬式)	20
7	ロジン	NULL

0以外を見つけたい時でも、NULLの事を書いてやらないとダメなんですね。
知っている人からすると簡単な話しですが、知っているのと使えるのはまた別かもしれませんので、
しょーもないところでバグを出して野球大会で優勝を逃すようなことがないよう、慌てず落ち着いてSQL書くようにしましょう！