MySQL SELECT x FROM WHERE NOT IN (SELECT x FROM b) - Неожиданный результат

Я ожидаю, что результат третьего запроса ниже содержит id = 732. Это не так. Почему это?

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)

В таблице email.id есть три элемента NULL, а в match.id нет записей NULL.

Полная таблица/запросы можно увидеть на http://pastebin.ca/1462094

Ответ 1

Из документация:

Чтобы соответствовать стандарту SQL, IN возвращает NULL не только в том случае, если выражение с левой стороны равно NULL, но также и если в списке не найдено совпадений, а одно из выражений в список NULL.

Это именно ваш случай.

Оба IN и NOT IN возвращают NULL, что не является приемлемым условием для предложения WHERE.

Перепишите свой запрос следующим образом:

SELECT  *
FROM    match m
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    email e
        WHERE   e.id = m.id
        )

Ответ 2

... или если вы действительно хотите использовать NOT IN, вы можете использовать

SELECT * FROM match WHERE id NOT IN ( SELECT id FROM email WHERE id IS NOT NULL)

Ответ 3

Я немного оторван от деталей того, как MySQL имеет дело с нулями, но вот две вещи, которые нужно попробовать:

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

Второй запрос выглядит как интуитивно понятный, но он выполняет условие соединения, а затем условие where. Это тот случай, когда объединения и где предложения не являются эквивалентными.

Ответ 4

Вот какой SQL действительно имеет смысл:

SELECT m.id FROM match m LEFT JOIN email e ON e.id = m.id WHERE e.id IS NULL

Простой всегда лучше.