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

Answer 1

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
        )

Answer 2

... 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)