MySQL:NOT IN with sub select 没有按预期工作? [英] MySQL: NOT IN with sub select not working as expected?
问题描述
这是我的查询:
SELECT customer_email
FROM sales_flat_order
WHERE customer_email NOT IN (SELECT customer_email
FROM sales_flat_order
WHERE status != 'holded');
我的测试 customer_email
test@example.com 有 3 行状态为 holded
,该邮件没有其他状态.出于某种原因,完整查询不返回匹配项.当我像这样手动填写 NOT IN 时,它起作用了,我得到了 3 行:
There are 3 rows with status holded
for my test customer_email
test@example.com, 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', '123@456.com');
那么我在这里做错了什么?
So what am I doing wrong here?
小提琴:https://dbfiddle.uk/?rdbms=mysql_5.6&小提琴=f990a09528d82d7bb4e72530a5de59ec
虽然小提琴按预期工作,但我的桌子要大得多,但列的类型相同.
The fiddle works as expected though, my table is much bigger, but the columns are of the same type.
谢谢!
推荐答案
我将假设 sales_flat_order
中至少有一条记录满足条件 status != 'holded'
并且其 customer_email
为 NULL
.
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
对于 NULL
s 是出了名的棘手,这里有一个例子.
(NOT) IN
is notoriously tricky with NULL
s, here is an example.
考虑以下查询:
SELECT 1 WHERE 1 NOT IN (SELECT 2 UNION ALL SELECT 3)
正如预期的那样,这会产生一个值为 1
的记录.
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)
然后查询产生一个空的结果集.这是(NOT) IN
的一个众所周知的问题.出于这个原因,您通常应该避免这种语法,并使用 (NOT) EXISTS
代替.上面的查询可以改写为:
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
)
对于您的查询:
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'
);
这篇关于MySQL:NOT IN with sub select 没有按预期工作?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!