MySQL "NOT IN" not working

Question

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 `where (date >= "2012-01-01") and (key not in ...)`; MySQL docs are vague on the `not in` operator and state that `expr NOT IN (value,...) is the same as NOT (expr IN (value,...))`, which might result in `NOT((date >= "2012-01-01" and key) IN (...))` in your case — lanzz, Jun 06 '12 at 12:50
If the subquery returns null (in your case if some_key is null) then the "not in" clause does not work. Filter subquery such that some_key is not null — AdityaKapreShrewsburyBoston, Nov 03 '14 at 05:20

score 32 · Accepted Answer · answered Jun 06 '12 at 12:49

32

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

answered Jun 06 '12 at 12:49

Vishwanath Dalvi

35,388
41
123
155

Nice, they both work and seem to take about the same amount of time. Thanks! – jeffery_the_wind Jun 06 '12 at 13:09
I would have up-voted this answer, but it only gives alternatives to the original approach. It doesn't explain why the original approach doesn't work. The answer from @Kibbee does explain what the problem is. – Syntax Junkie Feb 14 '23 at 17:23

score 18 · Answer 2 · answered Jun 06 '12 at 12:50

Most likely you have some NULL values in your "key" column. NULL comparisons always return null, which evaluates to false. This can be counter intuitive. For example

SELECT * FROM MyTable WHERE SomeValue <> 0

Would not return the values with SomeValue = NULL. Even though intuitively, NULL does not equal zero. So to fix the query you have, you should probably do the following.

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

In my case there also where null values in -> not in array query. Thanks — Irakli, Jun 14 '21 at 12:35

score 1 · Answer 3 · answered Jun 06 '12 at 12:49

1

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

answered Jun 06 '12 at 12:49

Sarfraz

377,238
77
533
578

MySQL "NOT IN" not working

3 Answers3