SQLのNULLを知り、正しく向き合う

サンプルはMySQLを利用しています。

TL;DR

• SQLにおけるNULLの意味を理解する。
• NOT NULL制約を用いて、NULLの存在を排除する。
• NULLがどうしても必要な場合は関係者間で意味を統一する。

データベースでのNULLで起こる問題の事例

SQLにおけるNULLが何であるかを学習する前にNULLが問題となる簡単な事例を紹介します。

文字列の結合

次のようなテーブル（staff）があります。

staff_id	first_name	last_name
1	太郎	[NULL]
2	花子	山田

太郎の苗字はNULLになっています。

次のように氏名を取得するSQLを実行します。

SELECT
  CONCAT(last_name, first_name) AS full_name
FROM
  staff;

結果はどのようになるでしょうか。

full_name
[NULL]
山田花子

太郎は値があるにもかかわらず表示されませんでした。

Oracleでは文字列の結合時にNULLを空文字で扱うため、太郎と出力される。

NULLを含むカラムの検索

次のようなワクチン接種記録テーブル（vaccination_history）があります。

patient_id	num_vaccinated	last_vaccinated_date
1	[NULL]	[NULL]
2	3	2022-06-01 00:00:00
3	2	2022-01-01 00:00:00

プライマリーキー（patient_id）以外ではNULLが許容されています。
patient_id:1は未接種なので記録がなく、num_vaccinated（ワクチンの接種回数）、last_vaccinated_date（最後の接種日）共にNULLです。

次のように接種が3回未満を取得するSQLを実行します。

SELECT
  *
FROM
  vaccination_history
WHERE
  num_vaccinated < 3;

patient_id	num_vaccinated	last_vaccinated_date
3	2	2022-01-01 00:00:00

未接種のpatient_id:1は検出できませんでした。

異なる解釈

現場では、複数のチームがテーブルを参照します。

単純化した例ですが、次のような解釈の相違で障害に発展したことがありました。

チーム	未設定とは？
オンライン	[NULL]
バッチ	空文字

この相違は結合試験実施時に判明（バッチ側でNullPointer例外が発生しアベンド）し、オンライン側が横展開を含めた決して少なくない工数を割くことになりました。

3値論理とNULL

3値論理は非常に深いトピックでさかのぼれば哲学者アリストテレスやオッカムに行きつくようです。

深い考察は専門の書籍に譲り、ここでは次の内容を理解します。

SQLの真理値はtrue、false、そしてunknownである

unknownはNULLが存在するときに発生します。

先述のワクチンの接種記録の例をもう一度見てみましょう。

patient_id	num_vaccinated	last_vaccinated_date
1	[NULL]	[NULL]
2	3	2022-06-01 00:00:00
3	2	2022-01-01 00:00:00

SELECT
  *
FROM
  vaccination_history
WHERE
  num_vaccinated < 3;

patient_id	num_vaccinated	last_vaccinated_date
3	2	2022-01-01 00:00:00

未接種のpatient_id:1は検索結果に出力されませんでした。
WHERE句は対象がtrueになるものを出力するため、検索時にunknown（NULL < 3）となるpatient_id:1は出力しません。

真理値表にすると次のようになります。

A	B	A AND B	A OR B	NOT A
T	T	T	T	F
T	F	F	T	F
T	U	U	T	F
F	T	F	T	T
F	F	F	F	T
F	U	F	U	T
U	T	U	T	U
U	F	F	U	U
U	U	U	U	U

T:true F:false U:unknown

なかなか直感的ではないというか、難解ですね。

正しく理解してIS NULLを活用すれば出力結果をコントロールできます。

SELECT
  *
FROM
  new_schema.vaccination_history
WHERE
  num_vaccinated < 3 OR num_vaccinated IS NULL;

patient_id	num_vaccinated	last_vaccinated_date
1	[NULL]	[NULL]
3	2	2022-01-01 00:00:00

未接種のpatient_id:1が表示されました。

ただこの場合、規定値として未接種を次のように設定するとどうでしょうか。

patient_id	num_vaccinated	last_vaccinated_date
1	0	1900-01-01 00:00:00
2	3	2022-06-01 00:00:00
3	2	2022-01-01 00:00:00

SELECT
  *
FROM
  vaccination_history
WHERE
  num_vaccinated < 3;

patient_id	num_vaccinated	last_vaccinated_date
1	0	1900-01-01 00:00:00
3	2	2022-01-01 00:00:00

期待どおりの結果になりました。
他にも問題点は多くありますが、ここではNULLはいない方がいいと感覚的に覚えてください。

NULLを使わないことで対処する方法

NULLの影響を回避する最良の方法はテーブルにNOT NULL制約を設けることです。
先述の例のように新規挿入時にはデフォルト値を適用するのもよいでしょう。
NULLを置き換える例を表にまとめます。

値の種類	解決法	備考
コード値文字列	不明を表すコードを追加	設定したコード値とは外れた9999などのコード値を不明な値として定義する。たとえば、ISOの性別コードであれば、不明は0、N/Aは9。
名前	共通の名無し名を作る	たとえば製品名は決まっていないが製品コードのみで登録するなど。その際はTBDなどが適切。商品名でTBDが使われる可能性の有無は合意しておく。
日付	最大値・最小値で代替	「0001-01-01」や「9999-12-31」を設定する
数値	不明の場合は0	0が適用できない場合は数値を利用したカラムの定義に問題があるか見直したい

参考：達人に学ぶSQL徹底指南書　第2版　初級者で終わりたくないあなたへ

NULLが必要な場合を考える

著名なRDBエキスパートジョー・セルコ氏の著書Joe Celko's SQL for Smarties: Advanced SQL Programmingでは「未知の数量とゼロの数量には違いがあります。それは、空の燃料タンクとまったく車を持っていないことの違いです。（※）」と述べています。

※ There is a difference between an unknown quantity and a zero quantity; it is the difference between an empty gas tank and not having a car at all.

これを区別するためにたとえば、次のような実装が考えられます。

• 空の燃料タンクの数量　0
• 車なし　NULL

セルコ氏の例を前提にすると上記のようになりますが、そもそも車がないというステータスを同じテーブルに登録しなければ問題になりません。
設計時にNOT NULL制約を前提に進めれば、多くのNULLの問題は回避できます。
また、NULLが必要な場合もテーブル定義書に意味を記載するなど関係者間で共有することが重要です。

まとめ

SQLのNULLは普段Javaなどで利用するNULLとは異なることが理解できたと思います。
すべての人が理解し、上手に付き合うことができればいいですが、現実はそうなりません。
極力NOT NULL制約を用いて、NULLの存在を排除することを推奨します。

• SQLにおけるNULLの意味を理解する。
• NOT NULL制約を用いて、NULLの存在を排除する。
• NULLがどうしても必要な場合は関係者間で意味を統一する。

参考書籍

• 達人に学ぶSQL徹底指南書　第2版　初級者で終わりたくないあなたへ
• Joe Celko's SQL for Smarties: Advanced SQL Programming
• SQL Antipatterns: Avoiding the Pitfalls of Database Programming