MySQL SELECT x FROM a WHERE NOT IN SELECT x FROM b - Unexpected result

Question

I expect the result of the third query below to contain id 732  It doesn t  Why is that    mysql  SELECT id FROM match ORDER BY id DESC LIMIT 5    ------------            id    ------------           732             730             655             458             456     ------------  5 rows in set  0 00 sec   mysql  SELECT id FROM email ORDER BY id DESC LIMIT 5    ------------            id    ------------           731             727             725             724             723     ------------  5 rows in set  0 00 sec   mysql  SELECT   FROM match WHERE id NOT IN   SELECT id FROM email     Empty set  0 00 sec    There are three NULL entries in table email id  and no NULL entries in match id   The full table   queries can be seen at http   pastebin ca 1462094

User · Answer

I m a little out of touch with the details of how MySQL deals with nulls  but here s two things to try   SELECT   FROM match WHERE id NOT IN        SELECT id FROM email WHERE id IS NOT NULL     SELECT     m   FROM     match m     LEFT OUTER JOIN email e ON         m id   e id         AND e id IS NOT NULL WHERE     e id IS NULL   The second query looks counter intuitive  but it does the join condition and then the where condition  This is the case where joins and where clauses are not equivalent

User · Answer

From documentation      To comply with the SQL standard  IN  returns NULL not only if the expression on the left hand side is NULL  but also if no match is found in the list and one of the expressions in the list is NULL    This is exactly your case   Both IN and NOT IN return NULL which is not an acceptable condition for WHERE clause   Rewrite your query as follows   SELECT    FROM    match m WHERE   NOT EXISTS                   SELECT  1         FROM    email e         WHERE   e id   m id

User · Answer

or if you really want to use NOT IN you can use  SELECT   FROM match WHERE id NOT IN   SELECT id FROM email WHERE id IS NOT NULL

User · Answer

Here is some SQL that actually make sense   SELECT m id FROM match m LEFT JOIN email e ON e id   m id WHERE e id IS NULL   Simple is always better

[sql] MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) - Unexpected result

Examples related to sql

Examples related to mysql