等しいものと等しくないものが全てではない

5 - 2 = 2 ?

users という5行のレコードを含むテーブルがあり、整数列 age の値が 20 と等しいレコードが2行だったとする。

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from users where age = 20;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

このとき、age の値が 20 と等しくないレコードは何行でしょうか？

5行 - 2行 = 3行だと期待して、where の条件を age <> 20 として試すと、

mysql> select count(*) from users where age <> 20;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

と期待通りにならない場合がある。　

解説

select where では、where の条件が真になった行、例えば

mysql> select 20 = 20;
+---------+
| 20 = 20 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

あるいは

mysql> select 18 <> 20;
+----------+
| 18 <> 20 |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

のような行を抽出する。一方で除外されるのは、条件が偽の行

mysql> select 18 = 20;
+---------+
| 18 = 20 |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

mysql> select 20 <> 20;
+----------+
| 20 <> 20 |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

だけでなく、null の行も除外される。

null と整数を比較すると、等しいかわからない、等しくないかわからないということで

mysql> select null = 20;
+-----------+
| null = 20 |
+-----------+
|      NULL |
+-----------+
1 row in set (0.01 sec)

mysql> select null <> 20;
+------------+
| null <> 20 |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)

のどちらも null になるので、age is null なレコードは where の条件が age = 20 でも age <> 20 でも除外されて、最初に書いた挙動になっていた。

こういう論理を3値論理と呼ぶとのこと。真か偽かの2値論理を想定して使ってしまうと落とし穴にはまることがあるので注意が必要。この話は書籍「SQLアンチパターン」の13章で紹介されていて、他には null と文字列を連結する場合の話も書かれています。

サンプルデータ

例として使ったテーブルは以下のようなもの。MySQL 5.7.16 で動作確認しました。

mysql> show create table users;
+-------+--------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `name` text NOT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from users;
+----------+------+
| name     | age  |
+----------+------+
| foo      |   18 |
| bar      |   20 |
| amnesiac | NULL |
| john     |   20 |
| mary     |   18 |
+----------+------+
5 rows in set (0.00 sec)