MySQLのNULLとは？NULLのレコードを特定・除く方法！

MySQLのNULLについて解説しています。

NULLについて書いた後に、NULLになっているカラムの行だけ取得する方法と、NULLがあるカラムの行は除く方法について書いています。
NULLのあるカラムの行を取得するにはIS NULLを使って、NULLがあるカラムの行を除くにはIS NOT NULLを使います。
その後に、実際にサンプルデータを使って動作を確認しました。

載せているSQLについては、MySQLのバージョン8.0.32を使って動作を確認しています。
公式ドキュメントにはこちらにNULLについての記載があります。

MySQLのNULLとは？

MySQLのNULLは存在しない値を意味します。

NULLのカラムにはデータが入っていないことになります。
大文字・小文字のどちらでも記載することが可能です。

NULLと空文字は違うのか？

NULLと空文字は違います。

NULLは値が設定されていないことですが、空文字は何も入っていない空の文字(”)が設定されています。
公式のこちらのドキュメントにもあるように、MySQLを始めたばかりの場合には間違えることがあるので気をつけましょう。

NULLのカラムを特定するには？

NULLのカラムを特定するには、IS NULLを使います。
例えば、SELECTでfruitsテーブルのpriceカラムにNULLが入っている行のみを取得したいときには、下記のように記載します。

SELECT * FROM fruits WHERE price IS NULL;

こうすると、fruitsテーブルのpriceカラムがNULLのレコードを取得してきます。

NULLのカラムを除外するには？

NULLのカラムを除外するには、IS NOT NULLを使います。
例えば、SELECTでpriceカラムにNULLが入っている行以外を取得したいときには、下記のように記載します。

SELECT * FROM fruits WHERE price IS NOT NULL;

こうすると、NOT NULLなので、priceカラムがNULLではないレコードを取得します。

NULLを入れさせないように制約をつける

CREATE TABLEでテーブルを作成するときに、カラムにNOT NULLを指定することで、NULLを入れれないように制約をつけれます。
例えば、下記のように定義することができます。

CREATE TABLE users (
    name VARCHAR(256) NOT NULL,
    age INT
);

こうすると、nameカラムにNULLを入れようとするとエラーが発生するようになり、データベースでNULLのデータを弾くことができます。
絶対にNULLが入ることを許したくないカラムには設定しておくと、プログラムでNULLを設定されようとしても入れれないため安心です。

参考：MySQLのNOT NULL制約とは？NULLを許可しない設定！

NULLを使ったサンプルSQL5つ！

NULL使った5つのサンプルSQLで挙動を確認してみます。

テストデータ

今回テストで使うテーブルは、下記のSQLで作りました。

CREATE TABLE fruits (
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(256),
  color VARCHAR(256),
  price INT,
  PRIMARY KEY (id)
);

データは下記のように入れています。

IS NULLを使う

IS NULLを使って、実際にNULLがあるレコードしか取得できないか試します。
こちらのSQLを実行してみます。

SELECT * FROM fruits WHERE price IS NULL;

解説のときに、記載したpriceカラムがNULLのレコードを取得するSQLです。
実行すると、下記のようになりました。

mysql> SELECT * FROM fruits WHERE price IS NULL;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  2 | apple  |  NULL |
|  5 | orange |  NULL |
+----+--------+-------+
2 rows in set (0.00 sec)

priceカラムがNULLのレコードのみ取得できました。

IS NOT NULLを使う

IS NOT NULLを使って、priceカラムがNULLではないレコードのみを取得してみます。
こちらのSQLを実行してみます。

SELECT * FROM fruits WHERE price IS NOT NULL;

実行すると、下記のようになりました。

mysql> SELECT * FROM fruits WHERE price IS NOT NULL;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | banana |   150 |
|  3 | melon  |   300 |
|  4 | grape  |   400 |
+----+--------+-------+
3 rows in set (0.00 sec)

先ほどとは反対に、priceに値が入っているレコードのみが取得できました。

NOT NULLのカラムに値を入れてみる

NOT NULL制約がついてるレコードに値を入れてみます。

こちらは、下記のテーブルに対して操作します。

CREATE TABLE users (
    name VARCHAR(256) NOT NULL,
    age INT
);

nameカラムに対して、NULLのデータを入れようとしてみます…
実行すると、下記の結果になりました。

> INSERT INTO users (name, age) VALUE (NULL, 18);
ERROR 1048 (23000): Column 'name' cannot be null

ERROR 1048 (23000)エラーが発生しました。
nameカラムにNULLを入れることができないためです。

NULLと空文字が違うのかの検証

次はNULLと空文字が違うのかの検証です。
fruitsテーブルのデータを下記のように修正しました。

idが2・4のレコードのnameカラムが空文字です。
idが3・5のnameカラムがNULLです。(明示的にNULLと記載しています)

nameカラムがNULLのデータを取得してみます。

mysql> SELECT * FROM fruits WHERE name IS NULL;
+----+------+-------+
| id | name | price |
+----+------+-------+
|  3 | NULL |   300 |
|  5 | NULL |   210 |
+----+------+-------+
2 rows in set (0.01 sec)

条件を「name IS NULL」とNULLのレコードのみ取得するようにしました。
NULLを設定したレコードのみが返ってきました。

次はnameカラムが空文字のレコードのみを取得します。

mysql> SELECT * FROM fruits WHERE name = '';
+----+------+-------+
| id | name | price |
+----+------+-------+
|  2 |      |   120 |
|  4 |      |   400 |
+----+------+-------+
2 rows in set (0.00 sec)

条件を「name = ”」として、nameカラムが空文字のレコードを取得しました。
このように、NULLと空文字が違うことが明確になったかと思います。

NULLのカラムを対象に更新する

最後にNULLのカラムを更新(アップデート)したいときです。
「IS NULL」を指定して、UPDATE文を実行すると良いです。

先ほどのfruitsテーブルに対して、NULLのnameカラムを全て更新するようにしました。

UPDATE fruits SET name = '何かのフルーツ' WHERE name IS NULL;

実際に実行して、レコードを確認します。

mysql> UPDATE fruits SET name = '何かのフルーツ' WHERE name IS NULL;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM fruits;
+----+-----------------------+-------+
| id | name                  | price |
+----+-----------------------+-------+
|  1 | banana                |   150 |
|  2 |                       |   120 |
|  3 | 何かのフルーツ          |   300 |
|  4 |                       |   400 |
|  5 | 何かのフルーツ          |   210 |
+----+-----------------------+-------+
5 rows in set (0.00 sec)

NULLのカラムのみ、しっかりと更新されたことが確認できました。

MySQLのNULLについてのまとめ

今回はMySQLのNULLについての解説と、サンプルSQLから挙動を確認していきました。
記事の内容をまとめると、下記のようになります。