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).
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.
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;
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 )
I guess either e.login or email_login has a CHAR type instead of VARCHAR. Hence that field is padded with blanks.
SELECT *
FROM emails e
WHERE e.error <> 1
AND NOT EXISTS (
SELECT * FROM accounts a
WHERE a.email_login = e.login
)
;
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)))
not exists?LEFT JOINoperator...UPPER(e.login) NOT IN ( SELECT UPPER(email_login) FROM accounts WHERE email_login IS NOT NULL )