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 NULL
s, 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'
);
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…