subquery - MySQL: NOT IN with sub select not working as expected?

Question

This is my query:

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

There are 3 rows with status holded for my test customer_email [email protected], no other status for that mail. For some reason, the full query returns no matches. When I fill the NOT IN manually like that, it works, I get my 3 rows:

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

So what am I doing wrong here?

Fiddle: https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=f990a09528d82d7bb4e72530a5de59ec

The fiddle works as expected though, my table is much bigger, but the columns are of the same type.

Thanks!

Answer 1

I will make the assumption that there is at least one record in sales_flat_order that satisfies condition status != 'holded' and whose customer_email is NULL.

(NOT) IN is notoriously tricky with NULLs, here is an example.

Consider the following query:

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

This yields a record with value 1, as expected.

However if you change that to:

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

Then the query produces an empty result set. This is a well-known problem with (NOT) IN. For this reason, you should generally avoid this syntax, and use (NOT) EXISTS instead. The above query could be rewritten as:

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
)

Demo on DB Fiddle

For your query:

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'
);