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

mysql, sql

Solution

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
        )

Problem

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

Original source