MySQL: NOT IN с дополнительным выбором не работает должным образом?

Это мой запрос:

SELECT customer_email 
FROM   sales_flat_order 
WHERE  customer_email NOT IN (SELECT customer_email
                              FROM   sales_flat_order
                              WHERE  status != 'holded');

Есть 3 строки со статусом holded для моего теста customer_email [email protected], других статусов для этого письма нет. По какой-то причине полный запрос не возвращает совпадений. Когда я заполняю NOT IN вручную, это работает, я получаю свои 3 строки:

SELECT customer_email 
FROM   sales_flat_order 
WHERE  customer_email NOT IN ('whatever', 'foobar', '[email protected]');

Так что я делаю неправильно здесь?

Скрипт: https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=f990a09528d82d7bb4e725 >

Однако скрипка работает, как и ожидалось, моя таблица намного больше, но столбцы одного типа.

Спасибо!


mysql subquery
person Arnie    schedule 26.11.2019    source источник
comment
См.: Почему я должен предоставлять MCRE для того, что мне кажется очень простым SQL-запросом?   -  person Strawberry    schedule 26.11.2019
comment
Вы должны предоставить некоторые образцы данных здесь.   -  person Tim Biegeleisen    schedule 26.11.2019
comment
Скрипка предоставлена ​​​​в моем посте, извините.   -  person Arnie    schedule 26.11.2019

Ответы (1)


arrow_upward
1
arrow_downward

Я сделаю предположение, что в sales_flat_order есть хотя бы одна запись, удовлетворяющая условию status != 'holded' и customer_email которой соответствует NULL.

(NOT) IN общеизвестно сложна с NULLs, вот пример.

Рассмотрим следующий запрос:

SELECT 1 WHERE 1 NOT IN (SELECT 2 UNION ALL SELECT 3)

Это дает запись со значением 1, как и ожидалось.

Однако, если вы измените это на:

SELECT 1 WHERE 1 NOT IN (SELECT 2 UNION ALL SELECT NULL)

Затем запрос выдает пустой результирующий набор. Это известная проблема с (NOT) IN. По этой причине вам обычно следует избегать этого синтаксиса и вместо этого использовать (NOT) EXISTS. Приведенный выше запрос можно переписать так:

SELECT 1 a
FROM (SELECT 1 a) t1
WHERE NOT EXISTS (
    SELECT 1
    FROM (SELECT 2 a UNION ALL SELECT NULL) t2
    WHERE t1.a = t2.a
)

Демонстрация скрипта БД

Для вашего запроса:

SELECT customer_email 
FROM sales_flat_order s
WHERE NOT EXISTS (
    SELECT 1
    FROM sales_flat_order s1
    WHERE s1.customer_email = s.customer_email AND s.status != 'holded'
);
person GMB    schedule 26.11.2019
comment
Спасибо, вы правы, в БД есть NULL! Этот запрос значительно медленнее? - person Arnie; 26.11.2019
comment
@Arnie: нет, я не ожидаю, что это будет медленнее, чем ваш текущий запрос. Для производительности вам нужен индекс на sales_flat_order(customer_email, status ) - person GMB; 26.11.2019