MySQL, PostgreSQLのnullに関する比較
概要
MySQLとPostgreSQLのnullの扱いについて解説します。
算術比較演算子の結果など共通する部分もありますが、ORDER BYによる結果の順番が異なるなど仕様が異なる点もあります。
比較する内容は以下の通りです。
- 算術比較演算子の結果
- NULLか調べる演算子
- ORDER BYでの扱い
なお、比較したMySQL, PostgreSQLのバージョンは以下です。
- MySQL: 8.0
- PostgreSQL: 15.4
比較表
ざっくりまとめると以下です。
| 観点 | MySQL | PostgreSQL |
|---|---|---|
算術比較演算子(=など)の結果 |
NULL |
NULL |
| NULLを調べる演算子 |
IS NULL, IS NOT NULL
|
IS NULL, IS NOT NULL, ISNULL, NOTNULL
|
ORDER BYでの扱い（ASCの場合） |
最初（値があるカラムよりも前）に出力される。最後にするためにはcolumn_name IS NULL ASCを追加する |
最後（値があるカラムよりも後）に出力される。最初にするためにはNULLS FIRSTを追加する |
MySQL
以下のPlayGroundにて検証が可能です。
算術演算子の結果
MySQL :: MySQL 8.0 リファレンスマニュアル :: 3.3.4.6 NULL 値の操作
=, <, <>などの算術比較演算子の結果は常にNULLとなる。
$ SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
| -------- | --------- | -------- | -------- |
| null | null | null | null |
そのため、WHERE句において比較しても結果に出力されない。
$ SELECT * FROM users;
| id | name |
| --- | ---- |
| 1 | Taro |
| 2 | Jiro |
| 3 | |
$ SELECT * FROM users WHERE name <> 'Taro';
| id | name |
| --- | ---- |
| 2 | Jiro |
NULLを調べる演算子
MySQLではIS NULL演算子によって、値がNULLであるかどうかを調べることができる。
また、IS NOT NULL演算子によって、値がNULLではないかを調べることができる。
$ SELECT * FROM users WHERE name IS NULL;
| id | name |
| --- | ---- |
| 3 | |
ORDER BYでの扱い
NULLは非NULLの値よりも小さいと評価される。
そのため、ORDER BYでソートすると以下の位置に出力される。
- 昇順（
ASC）の場合：最初（値があるカラムよりも前） - 降順（
DESC）の場合：最後（値があるカラムよりも後）
$ SELECT * FROM users ORDER BY name ASC;
| id | name |
| --- | ---- |
| 3 | |
| 2 | Jiro |
| 1 | Taro |
nameカラムがNULLとなっているレコード(id: 3)が先になり、3, 2, 1という順番で出力された。
DESCの場合は、1, 2, 3という順番となる。
$ SELECT * FROM users ORDER BY name DESC;
| id | name |
| --- | ---- |
| 1 | Taro |
| 2 | Jiro |
| 3 | |
もしNULLの出力順序を入れ替えたい場合、ORDER BYの条件に<column_name> IS NULL ASC (または<column_name> IS NULL DESC)を追加する。
$ SELECT * FROM users ORDER BY name IS NULL ASC, name ASC;
| id | name |
| --- | ---- |
| 2 | Jiro |
| 1 | Taro |
| 3 | |
こうすると、nameカラムがNULLであるレコードが最後になり、2, 1, 3という順番で出力される。
DESCの場合は3, 1, 2という順番になる。
$ SELECT * FROM users ORDER BY name IS NULL DESC, name DESC;
| id | name |
| --- | ---- |
| 3 | |
| 1 | Taro |
| 2 | Jiro |
<column_name> IS NULL ASCで順番が入れ替わる理由は、<column_name> IS NULLの結果でソートしているから。
NULLのカラムはIS NULLの結果がtrue(1)となるため、昇順でソートすると後方になる。
$ SELECT *, name IS NULL FROM users ORDER BY name IS NULL ASC, name ASC;
| id | name | name IS NULL |
| --- | ---- | ------------- |
| 2 | Jiro | 0 |
| 1 | Taro | 0 |
| 3 | | 1 |
PostgreSQL
以下のPlayGroundで検証可能です。
算術演算子の結果
参考： 9.2. 比較関数および演算子
MySQLと同様に =, <, <>などの算術比較演算子の結果は常にNULLとなる。
$ SELECT 1 = NULL as "1 = NULL", 1 <> NULL as "1 <> NULL", 1 < NULL as "1 < NULL", 1 > NULL as "1 > NULL";
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
| -------- | --------- | -------- | -------- |
| | | | |
また、WHERE句で比較しても結果に出力されない。
$ SELECT * FROM users;
| id | name |
| --- | ---- |
| 1 | Taro |
| 2 | Jiro |
| 3 | |
$ SELECT * FROM users WHERE name <> 'Taro';
| id | name |
| --- | ---- |
| 2 | Jiro |
ここで、 PostgreSQLではNULLを比較して評価できる演算子が用意されている。
-
IS DISTINCT FROM: NULLを比較可能な値とした上で、等しくない。 -
IS NOT DISTINCT FROM: NULLを比較可能な値とした上で、等しい。
先程の例(<> 'Taro')では、nameがTaroではないレコードの結果はJiro(id: 2)のレコードのみ出力されていた。
しかし、IS DISTINCT FROM演算子を用いるとNULLのレコード(id: 3)も結果に出力することができる。
$ SELECT * FROM users WHERE name IS DISTINCT FROM 'Taro';
| id | name |
| --- | ---- |
| 2 | Jiro |
| 3 | |
これは以下の条件と同義となる。
$ SELECT * FROM users WHERE name <> 'Taro' or name IS NULL;
| id | name |
| --- | ---- |
| 2 | Jiro |
| 3 | |
NULLを調べる演算子
PostgreSQLでは、IS NULL, IS NOT NULL演算子によって、値がNULLであるかどうかを調べることができる。
さらに、非標準構文であるが、ISNULL, NOTNULL演算子でも調べられる。
$ SELECT * FROM users WHERE name IS NULL;
| id | name |
| --- | ---- |
| 3 | |
$ SELECT * FROM users WHERE name ISNULL;
| id | name |
| --- | ---- |
| 3 | |
ORDER BYでの扱い
MySQLと異なり、PostgreSQLでのNULLは非NULL値よりも大きいと評価される。
そのため、ORDER BYでソートすると以下の位置に出力される。
- 昇順（
ASC）の場合：最後（値があるカラムよりも後） - 降順（
DESC）の場合：最初（値があるカラムよりも前）
つまり、以下のような結果となる。
$ SELECT * FROM users ORDER BY name ASC;
| id | name |
| --- | ---- |
| 2 | Jiro |
| 1 | Taro |
| 3 | |
$ SELECT * FROM users ORDER BY name DESC;
| id | name |
| --- | ---- |
| 3 | |
| 1 | Taro |
| 2 | Jiro |
NULLの順番を指定したい場合は、NULLS FIRST, NULLS LASTを以下のように追加する。
$ SELECT * FROM users ORDER BY name ASC NULLS FIRST;
| id | name |
| --- | ---- |
| 3 | |
| 2 | Jiro |
| 1 | Taro |
$ SELECT * FROM users ORDER BY name DESC NULLS LAST;
| id | name |
| --- | ---- |
| 1 | Taro |
| 2 | Jiro |
| 3 | |
（番外編） Oracle
MySQLやPostgreSQLと異なり、空文字''とNULLは区別されない。
Oracleでは文字列型のNULLを長さが0(ゼロ)の文字列と区別できないという例外があります。
最後に
MySQLとPostgreSQLのNULLの扱いに関して比較して解説しました。
業務でMySQLのNULLについて調べた際、ふと「他のRDBMSだとどうなのだろうか」を思い調べてみたら、予想外に異なる点があったのでとても良い機会になりました。
Discussion