はじめに
NULL とは値が無いことを示しています。
初学者にとってはこの「値が無い」ということに違和感を覚えるかもしれません。
例えば、あなたが続けていた大好きなゲームをある日突然飽きて辞める日というのはまだ分からないですよね。
その場合に、ゲーム会社はあなた(user)の end_date 列(今回ならユーザー退会日)に代入する値が無い為、NULL が入る可能性が出てきます。
NULL は本来好まれるものではありません。バグの温床となりやすいからです。
この記事では、どうしても NULL を入れなきゃいけない場面を想定して、そこで注意するべき点を挙げています。
使用するテーブル例
今回、使用するテーブルは下記とします。
(備考)の部分は、先輩が何年生か、を分かりやすくするために示した欄でテーブルには加わっておりません。
この場合、3年生の Smith は最高学年なので senpai_user_id が NULL である、ということを頭の隅に置いていただけると幸いです。
| user_id | fname | lname | senpai_user_id | （備考) |
|---|---|---|---|---|
| 1 | Smith | Michael | NULL | 3年生 |
| 2 | Susan | Boylee | 1 | 2年生 |
| 3 | Brian | Jessee | 2 | 2年生 |
| 5 | Susannu | Rayy | 5 | 2年生 |
先輩がいないユーザーを検索する。(IS NULL)
手始めに、先輩がいない全てのユーザー、を検索してみます。
先輩がいないということは、つまり senpai_user_id = NULL ですね。3年生のデータ(Smith Michael)が取ってこれれば成功です。
式が NULL であるかどうかを評価するには、 IS NULL 演算子 を使用する必要があります。今回なら user_id の Smith が3年生で NULL 値が入っているため、Smith だけ出力されます。
mysql> SELECT user_id, fname, lname, senpai_user_id
FROM user
WHERE senpai_user_id IS NULL;
| user_id | fname | lname | senpai_user_id |
|---|---|---|---|
| 1 | Smith | Michael | NULL |
ここで、IS NULL ではなく = NULL を使って書いた場合
mysql> SELECT user_id, fname, lname, senpai_user_id
FROM user
WHERE senpai_user_id = NULL;
> Empty set (0.01sec)
クエリは解析されて実行されますが、行は返ってきません。
この記事の冒頭で、NULL は値が無いことを示します と書きましたが、つまり値が無い以上、それと = になるものなんて分からないよねと言っており、どんなクエリに対しても = NULL は empty を返します。
先輩が一人以上いるユーザーを検索する。(IS NOT NULL)
列に値が代入されているかどうかを確認したい場合、IS NOT NULL 演算子を使用することができます。下記の場合、senpai_user_id が NULL ではない一人以上の先輩がいる Susan, Brian, Susannu の３人が出力されます。
mysql> SELECT user_id, fname, lname, senpai_user_id
FROM user
WHERE senpai_user_id IS NOT NULL;
| user_id | fname | lname | senpai_user_id |
|---|---|---|---|
| 2 | Susan | Boylee | 1 |
| 3 | Brian | Jessee | 2 |
| 5 | Susannu | Rayy | 5 |
senpai_user_id に NULL がある故に起きそうな事故例
さて、この記事で一番伝えたいところです。
例えば、user_id 5 の Susannu 以外のユーザー全て検索したいとしましょう。
その場合、こんなクエリをあなたは書くかもしれません。
SELECT user_id, fname, lname, senpai_user_id
FROM user
WHERE senpai_user_id != 5
| user_id | fname | lname | senpai_user_id |
|---|---|---|---|
| 2 | Susan | Jon | 1 |
| 3 | Brian | Porker | 2 |
見事、Susannu を除いた二人の先輩ユーザーが出力されました。
しかし、これには欠点があります。それは最初に NULL 指定で出力していた
mysql> SELECT user_id, fname, lname, senpai_user_id
FROM user
WHERE senpai_user_id IS NULL;
| user_id | fname | lname | senpai_user_id |
|---|---|---|---|
| 1 | Smith | Michael | NULL |
3年生の Smith が抜けていることです。そのため senapi_user_id 列に NULL が含まれている可能性を考慮に入れるべきです。
mysql> SELECT user_id, fname, lname, senpai_user_id
FROM user
WHERE senpai_user_id != 5 OR senpai_user_id IS NULL;
| user_id | fname | lname | senpai_user_id |
|---|---|---|---|
| 1 | Smith | Michael | NULL |
| 2 | Susan | Boylee | 1 |
| 3 | Brian | Jessee | 2 |
見事、senpai_user_id 5の Susannnu ではない先輩ユーザーが含まれます。
NULL値の許容
senpai_user_id に NULL がある故に起きそうな事故例の項で、見事 Susannu ではない先輩ユーザーが出力できましたが、そもそも NULL を許容しないように設定すれば、これらは全て解消できることです。
しかし、今回の例では、Smith に先輩がいません。そんな時には、NULL を仕方なく代入する必要性が出てくるでしょう。
最初のDB設計時に、NULL を許容するかどうかを十分に検討していくことで、データの取得時に、クエリで困らないようにしていきますが、どうしても NULL を入れなきゃいけない場合もあります。
そのような時には、この記事での事故例を反面教師として、NULL値をどう扱うか、を検討していただけると良いと思います。
最後に
NULLって言い過ぎて、途中から「NULLってなんだっけ」ってなっていた。