mysqlでNULLのデータを判定する方法を調べてみた

はじめに

こんにちは、SHIFT の開発部門に所属しているmurasawaです。
中途で入社、バックエンド関連の開発を担当しています。

現在、認証基盤の開発を行っています。
開発の中でDB等、新たに学んだり得た知見をアウトプットし理解を深めていくとともに技術の共有として役に立てば幸いです。

今回、データベースのテーブルに格納されている値がNULLであるときの判定を行おうとして、間違ったやり方で行い意図した動きにならなかったのでその判定方法を調べまとめてみました。

使用環境 mysql:5.7.35

以下テーブルを用いて操作を行います。

mysql> DESC orders;
+------------+------------------+------+-----+-------------------+-----------------------------+
| Field      | Type             | Null | Key | Default           | Extra                       |
+------------+------------------+------+-----+-------------------+-----------------------------+
| id         | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| name       | varchar(255)     | YES  |     | NULL              |                             |
| price      | int(10)          | YES  |     | NULL              |                             |
| quantity   | varchar(255)     | YES  |     | NULL              |                             |
| created_at | datetime         | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at | datetime         | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.03 sec)

mysql> select * from orders;
+----+----------+-------+----------+---------------------+---------------------+
| id | name     | price | quantity | created_at          | updated_at          |
+----+----------+-------+----------+---------------------+---------------------+
|  1 | NULL     |  2000 | 1        | 2022-11-04 13:45:30 | 2022-11-04 13:45:30 |
|  2 | kato     |  3000 | 2        | 2022-11-04 13:45:47 | 2022-11-04 13:45:47 |
|  3 | sato     |  2500 | 5        | 2022-11-04 13:46:03 | 2022-11-04 13:46:03 |
|  4 | ito      |  1000 | 1        | 2022-11-04 13:46:22 | 2022-11-04 13:46:22 |
|  5 | sato     |  4000 | 2        | 2022-11-04 13:46:35 | 2022-11-04 13:46:35 |
|  6 | yamamoto |   700 | 3        | 2022-11-04 13:46:56 | 2022-11-04 13:46:56 |
|  7 | kato     |  2700 | 10       | 2022-11-04 13:47:17 | 2022-11-04 13:47:17 |
|  9 |          | NULL  | NULL     | 2022-11-04 14:17:27 | 2022-11-04 14:17:27 |
+----+----------+-------+----------+---------------------+---------------------+
8 rows in set (0.02 sec)

まずは直感的にNULL判定を行ってみる

まずは直感的にwhere句でNULLを指定して取得を試みます。

mysql> select * from orders where name = NULL;
Empty set

nameにNULLが存在するのに取得することができませんでした。

WHERE name = NULLの指定で取れない理由

NULLは全てのデータ型に存在する「中身が存在しない」場合に使われる用語です。

「中身が存在しない」と「空である」ことはプログラムの世界では同じではありません。

空であるということは空（例:'')が値として入っている状態であり、 NULLはそもそも値が入っていない状態です。

NULLは状態であって値ではないので上記のようにWHERE name = NULLで検索をかけても目的の情報は取得することができません。

NULLを比較の条件にする、あるいは含めるには後述するIS NULLなどを用いる必要があります。

NULLを指定した場合の判定の返り値

条件を指定してテーブルを検索した際、下記のように返り値には正誤判定(0/1)が返ってきます。 1であるところがname = satoさんの行、0であるところがname != sato行です。

そして一つNULLになっている箇所があります。
=や> <などの比較述語は、値と値を比較するためのものなので、値ではないNULLを比較しようとすると不明な結果（UNKNOWN）となります。
そのため、0/1ではなくNULLが返ってくることになります。

mysql> select name = 'sato' from orders;
+---------------+
| name = 'sato' |
+---------------+
| NULL          |
|             0 |
|             1 |
|             0 |
|             1 |
|             0 |
|             0 |
|             0 |
+---------------+
8 rows in set (0.02 sec)

ちなみに比較演算子の条件にNULLを使うと返ってくる値は

mysql> select name = null from orders;
+-------------+
| name = null |
+-------------+
| NULL        |
| NULL        |
| NULL        |
| NULL        |
| NULL        |
| NULL        |
| NULL        |
| NULL        |
+-------------+
8 rows in set (0.02 sec)

mysql> 

すべてNULLとなります。

そもそも値を条件に指定する比較演算子で値ではないものを指定したら、結果はすべて不明な結果（UNKNOWN）→　NULLになるのはいわれてみればその通りですね…

値が空の場合

上記テーブルのnameに空文字を入れたデータがあるので取得を試みます。

mysql> select * from orders where name = '';
+----+------+-------+----------+---------------------+---------------------+
| id | name | price | quantity | created_at          | updated_at          |
+----+------+-------+----------+---------------------+---------------------+
|  9 |      | NULL  | NULL     | 2022-11-04 14:17:27 | 2022-11-04 14:17:27 |
+----+------+-------+----------+---------------------+---------------------+
1 row in set (0.01 sec)

空文字は値なのでwhere name = ''で取得することができます。

NULLを条件にデータを取得する方法

IS NULL

NULLであることを条件にデータを取得する場合、is nullやis not nullを用います。

mysql> select * from orders where name is null;
+----+------+-------+----------+---------------------+---------------------+
| id | name | price | quantity | created_at          | updated_at          |
+----+------+-------+----------+---------------------+---------------------+
|  1 | NULL |  2000 | 1        | 2022-11-04 13:45:30 | 2022-11-04 13:45:30 |
+----+------+-------+----------+---------------------+---------------------+
1 row in set (0.02 sec)

nameの値がnullの行を取得できました。

反対にnullでない行を取得する場合は

mysql> select * from orders where name is not null;
+----+----------+-------+----------+---------------------+---------------------+
| id | name     | price | quantity | created_at          | updated_at          |
+----+----------+-------+----------+---------------------+---------------------+
|  2 | kato     |  3000 | 2        | 2022-11-04 13:45:47 | 2022-11-04 13:45:47 |
|  3 | sato     |  2500 | 5        | 2022-11-04 13:46:03 | 2022-11-04 13:46:03 |
|  4 | ito      |  1000 | 1        | 2022-11-04 13:46:22 | 2022-11-04 13:46:22 |
|  5 | sato     |  4000 | 2        | 2022-11-04 13:46:35 | 2022-11-04 13:46:35 |
|  6 | yamamoto |   700 | 3        | 2022-11-04 13:46:56 | 2022-11-04 13:46:56 |
|  7 | kato     |  2700 | 10       | 2022-11-04 13:47:17 | 2022-11-04 13:47:17 |
|  9 |          | NULL  | NULL     | 2022-11-04 14:17:27 | 2022-11-04 14:17:27 |
+----+----------+-------+----------+---------------------+---------------------+
7 rows in set (0.02 sec)

で取得できます。

この場合も空文字はnullではないので取得できています。

<=> NULL

nullにも対応した比較演算子でも取得することができます。

mysql> select * from orders where name <=> NULL;
+----+------+-------+----------+---------------------+---------------------+
| id | name | price | quantity | created_at          | updated_at          |
+----+------+-------+----------+---------------------+---------------------+
|  1 | NULL |  2000 | 1        | 2022-11-04 13:45:30 | 2022-11-04 13:45:30 |
+----+------+-------+----------+---------------------+---------------------+
1 row in set (0.02 sec)

IS NULLなどの判定の返り値

IS NULLなどを使った際の判定の返り値はどうなっているのでしょうか。

確認してみます。

mysql> select name is null from orders;
+--------------+
| name is null |
+--------------+
|            1 |
|            0 |
|            0 |
|            0 |
|            0 |
|            0 |
|            0 |
|            0 |
+--------------+
8 rows in set (0.02 sec)

mysql> 

IS NULLや<=>はNULLも考慮されたものなのでNULLなのかそうでないのかの正誤の値0/1が返ってきています。

その他NULLに関する操作

IFNULL関数

NULLの要素に対して置換をかけることができます。

mysql> select * from orders;
+----+----------+-------+----------+---------------------+---------------------+
| id | name     | price | quantity | created_at          | updated_at          |
+----+----------+-------+----------+---------------------+---------------------+
|  1 | NULL     |  2000 | 1        | 2022-11-04 13:45:30 | 2022-11-04 13:45:30 |
|  2 | kato     |  3000 | 2        | 2022-11-04 13:45:47 | 2022-11-04 13:45:47 |
|  3 | sato     |  2500 | 5        | 2022-11-04 13:46:03 | 2022-11-04 13:46:03 |
|  4 | ito      |  1000 | 1        | 2022-11-04 13:46:22 | 2022-11-04 13:46:22 |
|  5 | sato     |  4000 | 2        | 2022-11-04 13:46:35 | 2022-11-04 13:46:35 |
|  6 | yamamoto |   700 | 3        | 2022-11-04 13:46:56 | 2022-11-04 13:46:56 |
|  7 | kato     |  2700 | 10       | 2022-11-04 13:47:17 | 2022-11-04 13:47:17 |
|  9 |          | NULL  | NULL     | 2022-11-04 14:17:27 | 2022-11-04 14:17:27 |
+----+----------+-------+----------+---------------------+---------------------+
8 rows in set (0.02 sec)

mysql> select ifnull(name, 'unknown') from orders;
+-------------------------+
| ifnull(name, 'unknown') |
+-------------------------+
| unknown                 |
| kato                    |
| sato                    |
| ito                     |
| sato                    |
| yamamoto                |
| kato                    |
|                         |
+-------------------------+
8 rows in set (0.02 sec)

nameがNULLであった行のnameの値が'unknown'という文字列に置き換わりました。

終わりに

今回、基盤の開発でNULLの判定を行う際に誤った方法で条件文を記載し、想定するデータをとれないことがあったのでnullに関することを調べまとめました。

そもそもnullという状態がテーブルに必要なのかから考え、nullを扱う際は気を付けて開発を行いたいと思います。

参考

【SQL】意外とややこしいnullについての基本的知識とSQLのnot nullを分かりやすく解説。 | ポテパンスタイル

【あわせて読みたいこのブロガーの記事一覧】

「#SatoshiMurasawa」の新着タグ記事一覧｜note ――つくる、つながる、とどける。

---

執筆者プロフィール：Satoshi Murasawa
前社ではRPAツールの技術サポート、開発を1年半経験。 SHIFTでは、バックエンドエンジニアとして入社し、node.jsやmysqlに触れはじめた。 DBに漠然とした興味があり、勉強して部の中でDB関連で役割を持つことができたらよいなと思っています。

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/

PHOTO：UnsplashのPhillip Flores