mysql - 不存在和不在的不同结果

Question

我有两个非常相似的查询，但是它们返回不同的结果。

第一个:

select * from products p 
where p.val >= 999999 and 
  not exists 
    (select * from products p2 where p2.val < 999999 and p.user_id = p2.user_id);

第二个:

select * from products p
where p.val >= 999999 and
   p.user_id not in (select user_id from products p2 where p2.val < 999999);

第一个给了我正确的答案，而第二个给了我没有(零)结果。有没有可能是因为第二个查询中的子查询给出了太多的结果？

Answer 1

当心空值!

如果子查询中有 NULL，NOT IN 将不会像大多数人期望的那样工作。

如果将 NOT IN (...) 翻译成 NOT (... OR ...) 或 NOT，问题会更清楚... AND NOT ... 并应用 three-valued logic到结果表达式。

为了举例说明这一点，假设条件是 NOT IN (1, 2, NULL) 并且被检查的行的值为 3。

使用 NOT (... OR ...) 你会得到这个:

NOT (3=1 OR 3=2 OR 3=NULL)

括号中的前两个条件为假，最后一个未知。基于三值逻辑，析取结果将是未知的。根据同样的逻辑，未知数的反转也是未知数。 WHERE 子句中 unknown 的结果与 false 的结果相同，即不匹配。所以，你来了。

现在，如果你用 NOT ... AND NOT ... 重写 NOT IN，这就是你得到的:

NOT 3=1 AND NOT 3=2 AND NOT 3=NULL

前两项为真，最后一项未知(3=NULL 未知，其反转也未知)。同样，三值逻辑表示在这种情况下最终结果是未知的。你又来了。

因此，当一行的值不在子集中但子集中也包含空值时，要么不使用 NOT IN，要么过滤掉空值。