SQLでのNULLは取り扱い注意

# はじめに
CAMエンジニア Advent Calendar 2019 19日目の記事となります。
前回は @mori_keisuke さんの [shellの基礎構文](https://qiita.com/mori_keisuke/items/c2a5e04199b45619d904) でした。
記事一覧はこちらから↓
https://qiita.com/advent-calendar/2019/cam-inc

最近「[理論から学ぶ実践データベース入門](https://www.amazon.co.jp/%E7%90%86%E8%AB%96%E3%81%8B%E3%82%89%E5%AD%A6%E3%81%B6%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E5%AE%9F%E8%B7%B5%E5%85%A5%E9%96%80-%E3%83%AA%E3%83%AC%E3%83%BC%E3%82%B7%E3%83%A7%E3%83%8A%E3%83%AB%E3%83%A2%E3%83%87%E3%83%AB%E3%81%AB%E3%82%88%E3%82%8B%E5%8A%B9%E7%8E%87%E7%9A%84%E3%81%AASQL-WEB-PRESS-plus/dp/4774171972/ref=sr_1_2?adgrpid=52706263349&gclid=CjwKCAiAluLvBRASEiwAAbX3GRmyLyak-Vndrj15t_Cbhxrkr84CGUDQcmftB6zQNUW6ju5FqHfqRhoCC7wQAvD_BwE&hvadid=338539117825&hvdev=c&hvlocphy=1009300&hvnetw=g&hvpos=1t1&hvqmt=e&hvrand=13171980598234429542&hvtargid=aud-759242200046%3Akwd-334714834025&hydadcr=27263_11561109&jp-ad-ap=0&keywords=%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E5%AE%9F%E8%B7%B5%E5%85%A5%E9%96%80&qid=1576577318&sr=8-2)」や「[プログラマのためのSQL](https://www.amazon.co.jp/%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9E%E3%81%AE%E3%81%9F%E3%82%81%E3%81%AESQL-%E7%AC%AC4%E7%89%88-%E3%82%B8%E3%83%A7%E3%83%BC%E3%83%BB%E3%82%BB%E3%83%AB%E3%82%B3/dp/4798128023/ref=sr_1_1?adgrpid=75148024223&gclid=CjwKCAiAluLvBRASEiwAAbX3GU0woXebF5DorkD_CTBD6ixDAAVQYIv5E7HKh8kCaPfWu9CeH-yZIBoCw8IQAvD_BwE&hvadid=368664772213&hvdev=c&hvlocphy=1009300&hvnetw=g&hvpos=1t1&hvqmt=e&hvrand=10821663877577772750&hvtargid=aud-759242200046%3Akwd-810313597576&hydadcr=14089_10900239&jp-ad-ap=0&keywords=%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9E%E3%81%AE%E3%81%9F%E3%82%81%E3%81%AEsql&qid=1576577538&sr=8-1)」などの書籍を読んで、
SQLにおける NULL という概念について改めて考える機会があったので、
その内容についてまとめさせていただきました。

結論から言うと、**どうしても必要な場合を除いて、可能な限りNULLは使用すべきではない**という内容になっています。
# NULL の何が問題なのか
SQLでのNULLは、強力な意味を持ちます。
プログラミング言語の世界では、単に「空である」という認識で扱っても問題にならないことが多いですが、
その認識で捉えていると不自然なふるまいをすることがあるため、扱いには注意が必要です。

具体的な例をいくつか挙げていきます。

### NULL の比較

SQL で NULL であるかどうかの条件を書く場合、
`= NULL` や `!= NULL` と書くことはできません。
`IS NULL` や `IS NOT NULL` と書く必要があります。

```sql
SELECT
    `Country`.`Name`,
    `Country`.`Capital`
  FROM `Country`
  WHERE
    `Country`.`Capital` != NULL -- `Country`.`Capital` IS NOT NULL と書く必要があるため、エラーになる
;
```

SQL に日常的に触れている方であれば、当然の挙動として認識されているかと思いますが、
最初は不思議に感じた方も多いのではないでしょうか。

そもそもなぜこのように書き分けをする必要があるかというと、
SQL において NULL は、**不明または適応不能**な状態を表すものであり、
**そもそも値ですらない**という扱いを徹底しているからです。
不明だから一致しているかしていないかも不明なので、等号や不等号を使って比較をするということができません。

この、**不明または適応不能**であるという状態を論理的に厳密に表現しているということが、
SQL における NULL を考える上でとても重要な手がかりになってきます。


### 三値論理
boolean型の列要素に対してNULL許可がされている以下のテーブルを例に取ります。

```sql
-- テーブルを用意
CREATE TABLE `flags` (
  `flag_id` int(10) unsigned NOT NULL,
  `raised` boolean NULL DEFAULT NULL,
  PRIMARY KEY (`flag_id`)
);

-- レコードを３件挿入
INSERT INTO `flags` (`flag_id`, `raised`) VALUES
(1, true),
(2, false),
(3, NULL);
```

`raised` という boolean型のカラムを持つ `flags` テーブルを作成し、
`raised` カラムにそれぞれ true, false, NULL という値の入ったレコードを3件作成しました。

このとき、 `flags` のテーブルに対して、「 `raised` が true ではない」レコードを検索します。

直感的には `raised` が false, NULL で登録されている2件のレコードがヒットしてほしいです。
ところが結果は以下のようになります。

```sql
SELECT * FROM `flags` WHERE `raised` != true;
+---------+--------+
| flag_id | raised |
+---------+--------+
|       2 |      0 |
+---------+--------+
1 row in set (0.00 sec)
```

`raised` が false で定義されたレコードしかヒットしません。
**NULL は値が不明なので、 true でないかどうかも不明**なのです。

同様に「 `raised` が false ではない」レコードを検索した場合も、 `raised` が true のレコードの1件のみしかヒットしない結果となります。

また、 NULL は数値と比較しても結果は不明なので、
`NULL <= 10` や `NULL > 32` のような演算は、どんな数と比較したとしても真にも偽にもなりません。
その結果は NULL になります。


このことから、 SQL は true, false の二値論理ではなく、
そのどちらにも当てはまらない NULL という状態を加えた
true, false, NULL の**三値論理**での演算を前提にしていることがわかるかと思います。

三値論理での演算は、二値論理と比べて途端に複雑さを増します。
考える必要のある条件が複雑なほど、条件漏れなどの人為的なミスの可能性が増え、
書き上がるSQLの可読性の低下にもつながるということを考えると、
NULL許可のカラムを使う上でのデメリットの一つと言えます。

↓二値論理での真理値表例

| A | B | A AND B | A OR B |
|:-:|:-:|:-------:|:------:|
| T | T | T       | T      |
| T | F | F       | T      |
| F | T | F       | T      |
| F | F | F       | F      |

↓三値論理での真理値表例

| A | B | A AND B | A OR B |
|:-:|:-:|:-------:|:------:|
| T | T | T       | T      |
| T | F | F       | T      |
| T | N | N       | T      |
| F | T | F       | T      |
| F | F | F       | F      |
| F | N | F       | N      |
| N | T | N       | T      |
| N | F | F       | N      |
| N | N | N       | N      |



### NULL の伝播
NULLは、四則演算やSQL関数演算の結果のほとんどを NULL に変えてしまいます。
以下の演算の結果は全て NULL です。

+ 100 + NULL
+ NULL / 0
+ CONCAT('ABC', NULL)

上記例の通り、 0 で割った場合も結果は NULL になります。
文字列と NULL を結合させようとした場合も、結果は NULL です。

このように、対象のデータの一部に NULL が含まれている場合、
演算を重ねるごとに NULL がどんどん増殖してしまう可能性を含んでいます。

「**雑草が繁殖する**」というようなニュアンスを含んでよばれており、
演算結果を台無しにすることにもつながる性質を持っているため、
ときに厄介な性質として現れることがあります。


### インデックスが参照されない
`IS NULL` や `IS NOT NULL` をWHERE条件に指定した場合、**インデックスが効きません**。
単純にパフォーマンスの低下にもつながるというデメリットを持っています。

# NULL を回避するには？
### 正規化する
NULL を回避するのに最も有効な方法は、テーブルを正規化することです。
正規化についての詳しい説明は割愛しますが、
ざっくり説明すると、データの重複や矛盾を設計レベルで防ぎ、
論理的に正しいデータ構造を保つための設計理論・手法のことです。

正規化には第1から第6までの段階があり、それぞれの段階が、前の段階の条件を満たすことを条件に持っているのですが、
**第1正規化からすでに、NULLを含んではいけない**という条件を満たす必要があります。
つまり NULL 許可の設定が含まれている時点で、そのテーブルは正規化されているとは言えません。

全てのテーブルを正規化する必要はありませんが、
不可能なものを除き、適切に正規化することができれば、
よりよい状態でデータを保持することにも繋がります。

### COALESCE 関数を使う
NULL の影響を最小限に抑える方法として **COALESCE 関数** を使う方法があります。

この関数は、与えられた引数のうち、最初に見つかった NULL ではない値を返すという動作をするので、
例えば、SUM 関数で数値の集計をしたいときなど、
NULL を 0 に置換して集計を可能にする、などの動作をさせることが可能です。

```sql
-- `price` に NULL が含まれていても、その価格を0とすることができるので集計が可能
SELECT SUM(COALESCE(`price`, 0)) FROM `products`;
```

この例の他にも、 `OUTER JOIN` をすることによって NULL になってしまう項目に対しても、
COALESCE 関数を使って初期値のように置換するという使い方もできます。


# 最後に
SQL における NULL の扱いについて自分なりにまとめてみました。
自分自身、普段何気なく扱っていた NULL という概念ですが、
理論的な部分も含めて改めて学びなおしてみると、また違った見え方ができるようになるなと感じました。

この記事が DB 設計をされる方への、考え方の一つとして一助となれば幸いです。

> NULL は薬だと思ってほしい。正しく使っている限りは有用だが、乱用すれば全てをぶち壊す。最良の選択は、可能な限り使用を避けてどうしても使わざるを得ないときだけ、適切に使用することだ。
> 
> 「プログラマのためのSQL 第4版」 著 ジョー・セルコ 監訳 ミック

明日は @tmyy さんの記事です。

# 参考文献
+ [プログラマのためのSQL 第4版](https://www.amazon.co.jp/%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9E%E3%81%AE%E3%81%9F%E3%82%81%E3%81%AESQL-%E7%AC%AC4%E7%89%88-%E3%82%B8%E3%83%A7%E3%83%BC%E3%83%BB%E3%82%BB%E3%83%AB%E3%82%B3/dp/4798128023/ref=sr_1_1?adgrpid=75148024223&gclid=CjwKCAiAluLvBRASEiwAAbX3GU0woXebF5DorkD_CTBD6ixDAAVQYIv5E7HKh8kCaPfWu9CeH-yZIBoCw8IQAvD_BwE&hvadid=368664772213&hvdev=c&hvlocphy=1009300&hvnetw=g&hvpos=1t1&hvqmt=e&hvrand=10821663877577772750&hvtargid=aud-759242200046%3Akwd-810313597576&hydadcr=14089_10900239&jp-ad-ap=0&keywords=%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9E%E3%81%AE%E3%81%9F%E3%82%81%E3%81%AEsql&qid=1576577538&sr=8-1)
+ [理論から学ぶ実践データベース入門](https://www.amazon.co.jp/%E7%90%86%E8%AB%96%E3%81%8B%E3%82%89%E5%AD%A6%E3%81%B6%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E5%AE%9F%E8%B7%B5%E5%85%A5%E9%96%80-%E3%83%AA%E3%83%AC%E3%83%BC%E3%82%B7%E3%83%A7%E3%83%8A%E3%83%AB%E3%83%A2%E3%83%87%E3%83%AB%E3%81%AB%E3%82%88%E3%82%8B%E5%8A%B9%E7%8E%87%E7%9A%84%E3%81%AASQL-WEB-PRESS-plus/dp/4774171972/ref=sr_1_2?adgrpid=52706263349&gclid=CjwKCAiAluLvBRASEiwAAbX3GRmyLyak-Vndrj15t_Cbhxrkr84CGUDQcmftB6zQNUW6ju5FqHfqRhoCC7wQAvD_BwE&hvadid=338539117825&hvdev=c&hvlocphy=1009300&hvnetw=g&hvpos=1t1&hvqmt=e&hvrand=13171980598234429542&hvtargid=aud-759242200046%3Akwd-334714834025&hydadcr=27263_11561109&jp-ad-ap=0&keywords=%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E5%AE%9F%E8%B7%B5%E5%85%A5%E9%96%80&qid=1576577318&sr=8-2)