MySQL "NOT IN" does not work

Question

Strange thing. I am having a problem with my MySQL Community Server 5.1 installed in the NOT IN window. When I make this request:

select * from table1 where date >= "2012-01-01"; 

returns 582 rows

 select * from table1 where date >= "2012-01-01" and the_key in (select some_key from table2); 

returns 15 rows

so I expect the following query will return 582 - 15 = 567 rows

 select * from table1 where date >= "2012-01-01" and the_key not in (select some_key from table2); 

returns 0 rows

Why doesn't this last query return a row?

Answer 1

Try it.

 select * from table1 where date >= "2012-01-01" and `key` not in (select some_key from table2 where some_key is not null); 

Or using does not exist

  select * from table1 where date >= "2012-01-01" and not exists ( select some_key from table2 where table2.some_key = table1.key 

Answer 2

Most likely, you have several NULL values ​​in the key column. NULL comparisons always return null, which evaluates to false. This may be the opposite of the intuitive. for example

 SELECT * FROM MyTable WHERE SomeValue <> 0 

Do not return values ​​using SomeValue = NULL. Although intuitive, NULL is not zero. Therefore, to correct the request, you must do the following.

 select * from table1 where date >= "2012-01-01" and (key not in (select some_key from table2) OR key IS NULL); 

Answer 3

 select * from table1 where date >= "2012-01-01" and `key` not in (select some_key from table2);