Mysql returns no values when null values are present In the subquery

Question

I do have two tables table1 and table2. And their content as follows

mysql> select id from table1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> select id from table2;
+------+
| id   |
+------+
|  301 |
|    2 |
| NULL |
+------+
3 rows in set (0.00 sec)

when I hit the below query in mysql console it always returns empty set

select id 
from table1 
where id 
not in (select id from table2);

Empty set (0.00 sec)

Is there a reason when there are null values in the sub query the in and not in would malfunction....?

I've solved it by using the below query

select id 
from table1 
where id 
not in (select id from table2 where id is not null);
+------+
| id   |
+------+
|    1 |
|    3 |
|    4 |
+------+

3 rows in set (0.00 sec)

Just want to know

Thanks in advance :)

edit: This question tries to clear some air but not enough

Possible duplicate of [Why MYSQL IN keyword not considering NULL values](http://stackoverflow.com/questions/10810391/why-mysql-in-keyword-not-considering-null-values) — kamal pal, Jun 11 '16 at 13:25
Welcome to the world of *Three-Valued-Logic*: https://en.wikipedia.org/wiki/Three-valued_logic#Application_in_SQL — dnoeth, Jun 11 '16 at 16:58

score 4 · Accepted Answer · edited Jun 12 '16 at 19:45

That is how not in works. I recommend that you use not exists instead:

select id 
from table1 t1
where not exists (select 1 from table2 t2 where t1.id = t2.id);

Why does not in work this way? It is because of the semantics of not in. Remember that NULL in SQL (usually) means an unknown value. Hence, if you have a list of "(1, 2)" you can say that "3" is not in the list. If you have "(1, 2, unknown)" you cannot say that. Instead, the result is NULL, which is treated as false.

NOT EXISTS does not behave this way, so I find it more convenient to use.

Mysql returns no values when null values are present In the subquery

1 Answers1