はじめに
CAMエンジニア Advent Calendar 2019 19日目の記事となります。
前回は @mori_keisuke さんの shellの基礎構文 でした。
記事一覧はこちらから↓
https://qiita.com/advent-calendar/2019/cam-inc
最近「理論から学ぶ実践データベース入門」や「プログラマのためのSQL」などの書籍を読んで、
SQLにおける NULL という概念について改めて考える機会があったので、
その内容についてまとめさせていただきました。
結論から言うと、どうしても必要な場合を除いて、可能な限りNULLは使用すべきではないという内容になっています。
NULL の何が問題なのか
SQLでのNULLは、強力な意味を持ちます。
プログラミング言語の世界では、単に「空である」という認識で扱っても問題にならないことが多いですが、
その認識で捉えていると不自然なふるまいをすることがあるため、扱いには注意が必要です。
具体的な例をいくつか挙げていきます。
NULL の比較
SQL で NULL であるかどうかの条件を書く場合、
= NULL や != NULL と書くことはできません。
IS NULL や IS NOT NULL と書く必要があります。
SELECT
`Country`.`Name`,
`Country`.`Capital`
FROM `Country`
WHERE
`Country`.`Capital` != NULL -- `Country`.`Capital` IS NOT NULL と書く必要があるため、エラーになる
;
SQL に日常的に触れている方であれば、当然の挙動として認識されているかと思いますが、
最初は不思議に感じた方も多いのではないでしょうか。
そもそもなぜこのように書き分けをする必要があるかというと、
SQL において NULL は、不明または適応不能な状態を表すものであり、
そもそも値ですらないという扱いを徹底しているからです。
不明だから一致しているかしていないかも不明なので、等号や不等号を使って比較をするということができません。
この、不明または適応不能であるという状態を論理的に厳密に表現しているということが、
SQL における NULL を考える上でとても重要な手がかりになってきます。
三値論理
boolean型の列要素に対してNULL許可がされている以下のテーブルを例に取ります。
-- テーブルを用意
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件のレコードがヒットしてほしいです。
ところが結果は以下のようになります。
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 に置換して集計を可能にする、などの動作をさせることが可能です。
-- `price` に NULL が含まれていても、その価格を0とすることができるので集計が可能
SELECT SUM(COALESCE(`price`, 0)) FROM `products`;
この例の他にも、 OUTER JOIN をすることによって NULL になってしまう項目に対しても、
COALESCE 関数を使って初期値のように置換するという使い方もできます。
最後に
SQL における NULL の扱いについて自分なりにまとめてみました。
自分自身、普段何気なく扱っていた NULL という概念ですが、
理論的な部分も含めて改めて学びなおしてみると、また違った見え方ができるようになるなと感じました。
この記事が DB 設計をされる方への、考え方の一つとして一助となれば幸いです。
NULL は薬だと思ってほしい。正しく使っている限りは有用だが、乱用すれば全てをぶち壊す。最良の選択は、可能な限り使用を避けてどうしても使わざるを得ないときだけ、適切に使用することだ。
「プログラマのためのSQL 第4版」 著 ジョー・セルコ 監訳 ミック
明日は @tmyy さんの記事です。