MySQL Forums
Forum List  »  General

Re: Null records create vastly different results
Posted by: Bjørn Munch
Date: April 06, 2022 02:21AM

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)

Options: ReplyQuote


Subject
Written By
Posted
Re: Null records create vastly different results
April 06, 2022 02:21AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.