カラムにNULLを許容したくなったら私を見て
はじめに
テーブル設計してるときに、あーもうここNULL許容したらいい感じになるんじゃね？知らんけど。
と投げ出そうと思ったことありませんか？
実際に投げ出すことはないと思いますが、こんな思いがよぎった時のため
なぜ、NULLを許容することが基本的には良くない！とされているのか書き残していきます。
そもそもNULLって何よ
NULLには、未知・不明 (Unknown) の場合と適用不能・非存在 (Not Applicable, Inapplicable) の場合がある。例えば、名前がNULLの場合、未知のNULLは「名前はあるが分からない」ということを、適用不能のNULLは「名前が存在しない」（例えば、独身者の配偶者の名前）ことを示す。
ということらしい(wiki調べ)
未知・不明、適用不能・非存在 要は定義されていない、わからないということですね 🤔
今回はテーブル設計する上でのNULLということでSQLで見ていきます。
| id | name |
|---|---|
| 1 | 山田 |
| 2 | null |
SELECT * FROM users WHERE name = NULL; -- id:2のレコードは取得できない
このクエリは、nameがNULLのレコードを取得しようとしていますが、SQLではNULLとの比較は常にUNKNOWNとなるため望んだ結果は得られません。
NULLとの比較には IS NULL や IS NOT NULL を使う必要があります。
SELECT * FROM users WHERE name IS NULL;
この時点でNULLを許容していなければ、考えないといけないことを
1つ減らせるなと感じてしまいますね 👀
NULLを許容すると...
ここからはカラムに対してNULLを許容すると起きる問題について書き連ねていきます。
1. 三値論理による予期しない挙動
SQLはTRUEとFALSEの二値ではなく、NULLを含む三値論理（TRUE/FALSE/UNKNOWN）で評価されます。そのため、NULLが含まれると予期しない挙動を引き起こすことがあります。
| id | name | age |
|---|---|---|
| 1 | 山田 | 20 |
| 2 | 佐藤 | 45 |
| 3 | 鈴木 | null |
SELECT * FROM users WHERE age != 30;
このクエリは「年齢が30ではないユーザーを取得したい」という内容ですが
前述したとおり、SQLではNULLとの比較は常にUNKNOWN(不明)となるため鈴木は結果に含まれません。
これにより、「NULLの行が漏れる」という予期しないバグや「年齢が30ではない」という考え方そのものに複雑さが発生します。
2. 集計関数の影響
NULLは集計関数の動作にも影響を与えます。
| id | name | age |
|---|---|---|
| 1 | 山田 | 20 |
| 2 | 佐藤 | 45 |
| 3 | 鈴木 | null |
SELECT COUNT(*) FROM users;
SELECT COUNT(age) FROM users;
このクエリはどちらもレコード数を取得するクエリです。
count()の引数に *を指定場合はテーブル内のすべてのレコード数を取得します。
count()の引数にage(カラム)を指定した場合は、ageがNULL以外のレコード数を取得します。
「なぜか画面に出てる件数とテーブルのレコード数が合わない....」とかなる可能性を秘めているわけですね👀
そのほかのSUM()やAVG()などの集計関数もNULLを無視するので計算結果が意図しないものになる可能性があります。
3. INNER JOINでNULLの行が結合されない
このような家の情報を持ったhouseテーブルと住民情報をもったresidentsテーブルがあるとします。
| id | address |
|---|---|
| 1 | 舎人 |
| 2 | 新中野 |
| 3 | 亀有 |
| id | name | house_id |
|---|---|---|
| 1 | 山田 | 1 |
| 2 | 佐藤 | null |
| 3 | 鈴木 | 3 |
SELECT houses.address, residents.name
FROM houses
INNER JOIN residents ON houses.id = residents.house_id;
この2つのテーブルをINNER JOINした場合、佐藤さん (house_id = NULL) は結果から除外されます。
これは ON houses.id = residents.house_id でNULLとの比較が行われUNKNOWNとなり除外されています。
LEFT JOINを使用してすべての行を結合することや、COALESCE()を使用してNULLを別の値に置き換えることで回避できるものではありますが、正しく理解してJOINを使用しなければ予期せぬ結果となりえます。
4. ビジネスロジックの複雑化
アプリケーション側でNULLチェックが増え、コードの複雑性が上がりバグの原因になったりします。
例: アプリケーションコードでのNULL処理
/** nullを許容しているがためのチェック **/
if (is_null($user->age)) {
// nullの場合の処理
}
/** 本当にやりたいこと **/
if ($user->age >= 20) {
echo('お酒が飲めるぞ!!!!');
} else {
echo('お酒が飲めません!!!!');
}
このように、NULLを適切に扱うための追加処理が必要になります。
今回はシンプルな例ですが、実際はインシデントが発生した場合にどこでテーブルにNULLが入ったのか、このNULLは正しいものなのかを調べる必要があったりします。
辛いですね。
5. データの一貫性が崩れやすい
NULLを許容すると、本来必須であるべきデータが欠落する可能性があります。
例えば、画面からユーザーが自身の名前と年齢を入力するフォームがあり年齢の入力は必須だったとします。
悲しいことにロジックの誤りによって変数ageにはNULLが代入されてしまいました。
そうした結果、以下のクエリが実行されることになります。
INSERT INTO users (name, age) VALUES ('武田', NULL);
usersテーブルのageにNULLが入ってしまいましたね 👀
このサービスでは年齢の入力が必須になっているのでusersのデータを扱うときにNULLを考慮しているとは考えにくく、不具合が発生します。
NOT NULL制約が設定されていればSQL実行時にエラーとなり、少なくともおかしなデータが登録されることは防げますね。
まとめ
NULLを許容すると以下のような問題が発生します。
- SQLの三値論理により予期しない挙動を引き起こす
- 集計関数の結果に影響を与える
- JOINの仕方によっては意図しない結果になる
- アプリケーションの処理が複雑化する
- データの一貫性が崩れやすくなる
そのため、テーブル設計の際にはNULLを安易に許容せず、適切に制約を設けることが重要です。
必ずしもすべてのケースでNULLを除外することは難しいかもしれません。
入力が任意のデータを保持するカラムとか。
しかしながら、明確な意図をもって止むを得ずNULLを許容する場合も、上記の問題が発生するため手元に爆弾を抱えたことを忘れないようにしなければならないのもまた事実です。
Discussion