このすみノート

Webエンジニアが技術や趣味を書くブログです。

MySQLやPostgreSQLにおけるNULLは、不明な値であり計算に使うと結果がNULLになってしまうことがある

私はプログラミング言語におけるNULLは、「何もない値」または「何も示していない値」という定義で理解しています。 ところがSQLにおけるNULLでは、この単純な理解が通用しないことを知りました。

先に結論を言ってしまうと、「NULLとは不明な値のことである」という解釈で落ち着いたのですが。 これの何が問題なのか、解説します。

MySQLにおけるNULLとは、「存在しない不明な値」のことである

MySQLのリファレンスを読むと、NULLとは不明な値であることがわかります。

NULL値に慣れるまでは驚くかもしれません。
概念的には、NULLは「存在しない不明な値」を意味し、ほかの値とは多少異なる方法で扱われます。
https://dev.mysql.com/doc/refman/5.6/ja/working-with-null.html より

不明な値を直訳で解釈すると、「何が入っているのか分からない値」という意味になります。

NULLを算術(計算)で使うとNULLになる

一般的なプログラミング言語では、NULLを使った計算では警告や例外が発生するか、もしくは「0」という値に自動変換されて計算されます。

<?php
$a = 10 - null;
echo $a; // 10

ところがMySQLでは「10」にもならないし、エラーにもなりません。

mysql> SELECT 10 - NULL;
+-----------+
| 10 - NULL |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

この挙動はPostgreSQLでも同様で、「10 - NULL」はNULLになります。

postgres=# \pset null '(null)'
Null display is "(null)".
postgres=# SELECT 10 - NULL;
 ?column?
----------
   (null)
(1 row)

NULLは算術比較できない

MySQLのリファレンスを読み進めていくと、算術比較はできませんと書いてあります。

=、<、または <> などの算術比較演算子を使用して NULL をテストすることはできません。 https://dev.mysql.com/doc/refman/5.6/ja/working-with-null.html より

つまりNULLは不明な値なので、算術比較の結果もまた、不明になってしまうのだろうと私は解釈しました。

NULLとなりえる値を、計算で使うのはやめよう

MySQLやPostgreSQLにおけるNULLの挙動を総括すると、たとえテーブル定義の型が整数型(INT型)であったとしても、「NOT NULL」制約のないカラムをSQLでそのまま計算に使わないほうが良いと言えます。

ただ計算に使えない数値というのも困りますので、対処法を考えてみます。 私が思いつく限り、対処法は大きく分けて2つあります。

テーブル定義(DDL)にNOT NULLを追加する

SQL上で計算に使う値であれば、テーブル定義を「NOT NULL DEFAULT 0」にして、必ず数値がある状態にします。

値が「0」であれば算術に使うことができるため、想定外の計算トラブルは抑制できます。

IFNULL()などのSQL関数を使って、NULL値を回避する

MySQLにはIFNULL()という、SQL関数があります。 IFNULLとは、「もしNULLであれば」を判定する関数です。

これを使って、NULLであれば「0」に変換して計算します。

mysql> SELECT 10 - IFNULL(NULL, 0);
+----------------------+
| 10 - IFNULL(NULL, 0) |
+----------------------+
|                   10 |
+----------------------+

テーブル定義で「NOT NULL」制約を使えないのであれば、NULL判定をしてデフォルト値を与えましょう。 NULLを回避することによって、計算結果がNULLになってしまう問題を防げます。

さいごに

MySQLやPostgreSQLにおけるNULLとは、特異な値であることを学びました。 だからMySQLのリファレンスにも、「NULL値に慣れるまでは驚くかもしれません。」と、あえて書いてあるのだと推測します。

なおデータベースにおけるNULLの扱い多くのRDBMSで共通ですが、Oracleだけは特別みたいです。

以上の NULL に関する事柄のほとんどは、PostgreSQL に限らず、多くの RDBMS で共通ですが、重要な例外が1つあります。
Oracleでは、NULL を含む文字列結合において NULL を返しません。
つまり select 'abc' || null from dual; の結果は abc になります。
https://oss-db.jp/dojo/dojo_08 より

本記事ではデータベースにおけるNULLを取り上げましたが、NULL値の挙動や扱い方は、プログラミング言語によっても変わります。 プログラム内でNULLを使うときは、「各プログラミング言語の参考書やリファレンスで、NULLの挙動を確認しておくのが良さそう」です。