2020-03-09に投稿

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

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

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

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

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

NULL値に慣れるまでは驚くかもしれません。
概念的には、NULLは「存在しない不明な値」を意味し、ほかの値とは多少異なる方法で扱われます。
より

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

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は不明な値なので、算術比較の結果もまた、不明になってしまうのだろうと私は解釈しました。

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 になります。
より

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

Originally published at www.konosumi.net
ツイッターでシェア
みんなに共有、忘れないようにメモ

このすみ

エンジニア。ブログで、映画・ゲーム・読書の感想を書いたり、技術系の知見を発信しています。 技術書典5で「エンジニアアンチパターン」と「PHP中級者を目指す 〜言語を使いこなすための本〜」という本を出しました。

Crieitは個人で開発中です。 興味がある方は是非記事の投稿をお願いします! どんな軽い内容でも嬉しいです。
なぜCrieitを作ろうと思ったか

また、「こんな記事が読みたいけど見つからない!」という方は是非記事投稿リクエストボードへ!

こじんまりと作業ログやメモ、進捗を書き残しておきたい方はボード機能をご利用ください!

ボードとは?

関連記事

コメント