MysqlのNOT IN句はNULLが絡むと初見ではちょっと気付けない挙動をします。MySQL以外でどうなのかは知りませんが。
以下のような見やすいテーブルの話をします。
| id | num | name |
|---|---|---|
| 1 | 1 | hello |
| 2 | 2 | world |
| 3 | NULL | ! |
まずは以下のクエリと実行結果を御覧ください。
SELECT * FROM `mytable` WHERE `num` IN (1, 3);
id, num, name
1, 1, hello
はい、特に不審な点はありませんね。
では次。
NULLの霊圧が…消えた…？
SELECT * FROM `mytable` WHERE `num` NOT IN (1, 3);
id, num, name
2, 2, world
SELECT * FROM `mytable` WHERE `num` IN (1, 3, NULL);
id, num, name
1, 1, hello
どうやら値がNULLだとINにもNOT INにも引っかからなくなるようです。
ちなみに IN ('') や NOT IN ('')でもNULLは引っかからない。
そして誰もいなくなった
SELECT * FROM `mytable` WHERE `num` NOT IN (1, 3, NULL);
id, num, name
0件。
分かった、条件にNULLを含めなければ良いんだな
…と思うじゃん？
油断すると簡単に以下のようなクエリになったりします。
SELECT * FROM `another_table` WHERE `another_table`.`id` NOT IN(
SELECT `num` FROM `mytable`
);
この場合サブクエリ部分が(1, 2, NULL) となって1行も返ってこない悲しい結果になります。
回避方法
サブクエリ内でWHERE に IS NOT NULL を追加するなどしましょう。
SELECT * FROM `another_table` WHERE `another_table`.`id` NOT IN(
SELECT num FROM `mytable` WHERE `num` IS NOT NULL
);