MySQLのNOT INでNULLが含まれていると常にFALSEの結果となるらしい

参考：https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_NOT_IN
※ PostgreSQLの参考ですが、MySQLで試してみても同じだったので投稿させていただきました

-- nullが入ってない場合はtrue/false問題なし
select 
	1 in (1, 2, 3) -- true
   ,5 in (1, 2, 3) -- false
   ,1 not in (1, 2, 3) -- false
   ,5 not in (1, 2, 3); -- true

-- nullが入ってた場合は一致するのがない場合はnullになってしまう
select 
	1 in (1, 2, null) -- true
   ,5 in (1, 2, null) -- null！！！
   ,1 not in (1, 2, null) -- false
   ,5 not in (1, 2, null); -- null！！！

nullはfalseっぽい値なので、not inでnullがあるとtrueと返すのが不可能になってしまう！

select 'hoge' where 1 not in (1, null); -- 空の結果が変える
select 'hoge' where 5 not in (1, null); -- 空の結果が変える

---

例えばNOT INのサブクエリでnullが含まれてしまっても思った通りに動きません

create table hoge(n int);
insert into hoge
values (1),(2),(3),(4),(5),(6),(7);

create table fuga(i int);
insert into fuga
values (5),(null),(7);

-- inの場合は問題なく、fugaの5,7が一致する2レコードの結果になる
mysql> select *
    -> from hoge
    -> where n in (select * from fuga);
+------+
| n    |
+------+
|    5 |
|    7 |
+------+
2 rows in set (0.01 sec)

-- not inの場合はnullが含まれてしまうとtrueと返すことがないので、
-- 空の結果になってしまう
mysql> select *
    -> from hoge
    -> where n not in (select * from fuga);
Empty set (0.01 sec)

やり方はいろいろあると思いますが、例えば NOT EXISTSなどを使うなどすると良いと思いました

mysql> select *
    -> from hoge
    -> where not exists (select * from fuga where n = i);
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    6 |
+------+
5 rows in set (0.00 sec)

以上です。見ていただいてありがとうございました。m(_ _)m