Strange thing happening. I am having a problem with my MySQL Community Server 5.1 installed on windows NOT IN query. When I do this query:
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 would expect that the following query would 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 is this last query not returning any rows?
Try this.
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 not exists
select *
from table1
where date >= "2012-01-01" and not exists ( select some_key from table2 where table2.some_key = table1.key