MySQL NOT IN doesn't work

Question

I have this MySQL query:

select e.* 
from emails as e 
where e.error <> 1 
AND e.login NOT IN ( select email_login from accounts )

But it returns 0 rows.

select e.* 
from emails as e 
where e.error <> 1 
AND e.id NOT IN (select email_id from accounts )

Does work correctly.

email_login is varchar(255) and id is int(11).

You absolutely sure? what if you rewrite statement using `not exists` ? — vittore, Mar 20 '13 at 14:01
well, maybe case-sensitivity leads to your problem, try `...UPPER(e.login) NOT IN ( SELECT UPPER(email_login) FROM accounts WHERE email_login IS NOT NULL )` — Najzero, Mar 20 '13 at 14:02
If it works after you add `where email_login is not null` to subquery, you are a victim of [not in null comparison](http://stackoverflow.com/questions/129077/not-in-constraint-and-null-values). — Nikola Markovinović, Mar 20 '13 at 14:04
@vittore what about left joining on the accounts table and selecting based on `accounts.email_id IS NULL`? or maybe I've misread? — Kevin Brydon, Mar 20 '13 at 14:04
@Najzero: Thank you very much WHERE email_login IS NOT NULL is the solution :) — mitch, Mar 20 '13 at 14:07
@MichałK. as requested I posted an answer, but I Strawberry's solution is also working like a charm and (explain plan) should do plainly spoken: the same as the subquery in most cases — Najzero, Mar 21 '13 at 08:47
@Najzero: I know that it's work but I don't want use join's. As Strawberry said is a "religious objection" :) sorry... On the other hand your solution i closer to answer on my question but Strawberry's solution is a new query. — mitch, Mar 21 '13 at 20:30

score 3 · Accepted Answer · answered Mar 21 '13 at 08:46

3

As requested, my comment as an answer.

My brain said "case sensitive", my fingers added IS NOT NULL to the subquery automaticly. My smart fingers bypass NOT NULL problems in comparism :-).

Here is your working query:

SELECT e.* from emails AS e 
WHERE e.error <> 1 
AND UPPER(e.login) NOT IN 
(SELECTUPPER(email_login) FROM accounts WHERE email_login IS NOT NULL)

my fingers added the UPPER() to that string comparism this time too.

answered Mar 21 '13 at 08:46

Najzero

3,164
18
18

UPPERCASE is not necessary. In my case there is no option to be different data :) (email logins). – mitch Mar 21 '13 at 20:18

score 2 · Answer 2 · answered Mar 20 '13 at 14:05

2

   SELECT e.* 
     FROM emails e 
     LEFT
     JOIN accounts a
       ON a.email_login = e.login
    WHERE e.error <> 1
      AND a.email_login IS NULL;

answered Mar 20 '13 at 14:05

Strawberry

33,750
13
40
57

Thank you but the solution is WHERE email_login IS NOT NULL I don't want to use joins – mitch Mar 20 '13 at 14:22
1

Is that a moral or an emotional objection? – Strawberry Mar 20 '13 at 14:25
I am not sure but I think joins are very slow. – mitch Mar 20 '13 at 14:27
7

Ah, I see - a religious objection - based upon your beliefs. Well, fair enough ;-) – Strawberry Mar 20 '13 at 14:30
2

I heard while loops are slow too. After all they run the same block of code again and again! What's up with that? I'm not going to use loops anymore. ;-) – Bill Karwin Mar 21 '13 at 11:48
Yeah, down with loops and joins! – Strawberry Mar 21 '13 at 11:51

score 1 · Answer 3 · edited Dec 20 '13 at 02:11

1

Try with NOT EXISTS instead of NOT IN

like that:

select e.* 
from emails as e 
where e.error <> 1 
AND e.login NOT EXISTS (select email_login from accounts )

edited Dec 20 '13 at 02:11

Eric Leschinski

146,994
96
417
335

answered Mar 20 '13 at 14:07

echo_Me

37,078
5
58
78

score 0 · Answer 4 · answered Mar 20 '13 at 14:11

0

I guess either e.login or email_login has a CHAR type instead of VARCHAR. Hence that field is padded with blanks.

answered Mar 20 '13 at 14:11

Joop Eggen

107,315
7
83
138

CloudyMarble · Answer 5 · 2013-03-21T07:39:56.343

This could be because e.login is NULL, when comparing NULL valuse as in your case as NOT IN it allways returns false (See documentation: operator NOT LIKE which applies to NOT IN too).

You can check this by using COALESCE() which:

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

So in your case:

 SELECT e.* 
 FROM emails AS e 
 WHERE e.error <> 1 
 AND COALESCE(e.login,'') NOT IN (select email_login from accounts)

If this does not work try depending on how exact should be compared either the LIKE operator or the trimmed values to avoid differences because of leading or trailing spaces

With %LIKE%:

 SELECT e.* 
 FROM emails AS e 
 WHERE e.error <> 1 
 AND NOT EXISTS (SELECT email_login 
                 FROM accounts 
                 WHERE email_login LIKE CONCAT('%', e.login ,'%'))

Notice that the % matches any number of characters, including zero characters

To match the exact login:

 SELECT e.* 
 FROM emails AS e 
 WHERE e.error <> 1 
 AND NOT EXISTS (SELECT email_login 
                 FROM accounts 
                 WHERE UPPER(TRIM(email_login)) = UPPER(TRIM(e.login)))

-1 You can't put identifiers inside a string literal like that. Why give an answer of "try this" when you obviously haven't tried it? — Bill Karwin, Mar 20 '13 at 14:17
@BillKarwin thank you, fixed. And by the way it is ok to provide an idea answer even if the i did not try it. — CloudyMarble, Mar 21 '13 at 07:27
Ok, I reversed my downvote since you have corrected the answer. I don't agree that it's okay to post answers that are pure guesses, and grossly wrong. — Bill Karwin, Mar 21 '13 at 11:47

score 0 · Answer 6 · answered Mar 20 '13 at 14:48

0

SELECT * 
FROM emails e 
WHERE e.error <> 1
AND NOT EXISTS (
 SELECT * FROM accounts a
 WHERE a.email_login = e.login
 )
;

answered Mar 20 '13 at 14:48

wildplasser

43,142
8
66
109

MySQL NOT IN doesn't work

6 Answers6