Re: Null records create vastly different results

[Bjørn Munch]

It doesn't matter what you data is, as long as you have any NULL in the b table I believe this is correct behavior. Any comparison involving NULL gives NULL so the existence of a NULL in the in causes the entire where clause to resolve to NULL. And "where NULL" is treated as false.

mysql> select 5 not in (2, 3);
+-----------------+
| 5 not in (2, 3) |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.15 sec)

mysql> select 5 not in (2, 3, NULL);
+-----------------------+
| 5 not in (2, 3, NULL) |
+-----------------------+
| NULL |
+-----------------------+
1 row in set (0.15 sec)