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

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

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

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.

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.