#11 where a != 'sample' におけるNULLの挙動

先日アプリの不具合調査の際にコードを読んでいたが、よく理解できていなかったのでまとめてみました。 結論から言うとSQLのWHERE句で比較条件で = を使用する場合、NULLは評価されないです。

こちらの記事にもある通りですが、

NULLはデータの欠落を表すため、任意の値や別のNULLとの関係で等号や不等号は成り立ちません。

以下でMySQLPostgreSQLで実際にレコードを挿入して試してみました。

$ sudo docker run -e MYSQL_ROOT_PASSWORD=password mysql:latest
$ sudo docker exec -it d10510842887 bash
bash-4.4# mysql -u root -p password
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
bash-4.4# 
bash-4.4# 
bash-4.4# 
bash-4.4# mysql -u root -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.03 sec)

mysql> create database sampledb;
Query OK, 1 row affected (0.05 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sampledb           |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use sampledb;
Database changed
mysql> create table sample_table (
    -> id int,
    -> body varchar(50)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+--------------------+
| Tables_in_sampledb |
+--------------------+
| sample_table       |
+--------------------+
1 row in set (0.01 sec)

mysql> insert into sample_table (id,body) values (1,'test1');
Query OK, 1 row affected (0.06 sec)

mysql> insert into sample_table (id,body) values (2,'test2');
Query OK, 1 row affected (0.04 sec)

mysql> insert into sample_table (id,body) values (3,'test3');
Query OK, 1 row affected (0.04 sec)

mysql> insert into sample_table (id,body) values (4,NULL);
Query OK, 1 row affected (0.04 sec)

mysql> insert into sample_table (id,body) values (5,NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from sample_table;
+------+-------+
| id   | body  |
+------+-------+
|    1 | test1 |
|    2 | test2 |
|    3 | test3 |
|    4 | NULL  |
|    5 | NULL  |
+------+-------+
5 rows in set (0.00 sec)

mysql> select * from sample_tables where body is null;
ERROR 1146 (42S02): Table 'sampledb.sample_tables' doesn't exist
mysql> select * from sample_table where body is null;
+------+------+
| id   | body |
+------+------+
|    4 | NULL |
|    5 | NULL |
+------+------+
2 rows in set (0.01 sec)

mysql> select * from sample_table where body is not null;
+------+-------+
| id   | body  |
+------+-------+
|    1 | test1 |
|    2 | test2 |
|    3 | test3 |
+------+-------+
3 rows in set (0.00 sec)

mysql> select * from sample_table where body = null;
Empty set (0.01 sec)

mysql> select * from sample_table where body != null;
Empty set (0.00 sec)

mysql> select * from sample_table where body = 'test1';
+------+-------+
| id   | body  |
+------+-------+
|    1 | test1 |
+------+-------+
1 row in set (0.00 sec)

mysql> select * from sample_table where body != 'test1';
+------+-------+
| id   | body  |
+------+-------+
|    2 | test2 |
|    3 | test3 |
+------+-------+
2 rows in set (0.00 sec)
$ sudo docker run -e POSTGRES_PASSWORD=password postgres:latest
$ sudo docker exec -it c8dc79e0f625 bash
root@c8dc79e0f625:/# psql -U postgres
psql (15.2 (Debian 15.2-1.pgdg110+1))
Type "help" for help.

postgres=# \l
                                                List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+------------+------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
(3 rows)
sampledb=# \dt
Did not find any relations.
sampledb=# create table sample_table (
sampledb(#   id serial,
sampledb(#   body varchar (50)
sampledb(# );
CREATE TABLE
sampledb=# \dt
            List of relations
 Schema |     Name     | Type  |  Owner   
--------+--------------+-------+----------
 public | sample_table | table | postgres
(1 row)

sampledb=# insert into sample_table (id,sample) values (1,'test1');
ERROR:  column "sample" of relation "sample_table" does not exist
LINE 1: insert into sample_table (id,sample) values (1,'test1');
                                     ^
sampledb=# insert into sample_table (id,body) values (1,'test1');
INSERT 0 1
sampledb=# insert into sample_table (id,body) values (2,'test2');
INSERT 0 1
sampledb=# insert into sample_table (id,body) values (3,'test3');
INSERT 0 1
sampledb=# insert into sample_table (id,body) values (4,NULL);
INSERT 0 1
sampledb=# insert into sample_table (id,body) values (5,NULL);
INSERT 0 1
sampledb=# select * from sample_table;
 id | body  
----+-------
  1 | test1
  2 | test2
  3 | test3
  4 | 
  5 | 
(5 rows)

sampledb=# select * from sample_table where body is null;
 id | body 
----+------
  4 | 
  5 | 
(2 rows)

sampledb=# select * from sample_table where body is not null;
 id | body  
----+-------
  1 | test1
  2 | test2
  3 | test3
(3 rows)

sampledb=# select * from sample_table where body = null;
 id | body 
----+------
(0 rows)

sampledb=# select * from sample_table where body != null;
 id | body 
----+------
(0 rows)

sampledb=# select * from sample_table where body = 'test1';
 id | body  
----+-------
  1 | test1
(1 row)

sampledb=# select * from sample_table where body != 'test1';
 id | body  
----+-------
  2 | test2
  3 | test3
(2 rows)