mysql - 询问不在空集中的项目时返回空集

Question

我有一个查询，其行为方式是我意想不到的。

我有两个表，stagin_users 和 users。在这两个表中，我都有一个名为 name 的列。在 users 表中，name 的每个值都是 NULL。在 staging_users 中，我有 13 行没有 NULL 值。我正在尝试运行一个查询，在该查询中获取临时表中名称不在 users 表中的所有用户。

我写的查询是:

选择名称
来自 staging_users
WHERE name NOT IN (SELECT name FROM users);

在编写查询时，我没有返回任何结果。这种行为的原因是什么？

因为 users 表只有 NULL 值，我知道我可以说 WHERE name IS NOT NULL 并且我会得到相同的结果，但我希望这个查询能够工作针对表中的值，这些值恰好都是 NULL。

Answer 1

来自docs :

To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

和

expr NOT IN (value,...) is the same as NOT (expr IN (value,...)).

因此当您SELECTing NULL 值时.. NOT IN 返回NULL.. 所以没有行匹配。

你可以这样纠正:

SELECT name 
  FROM staging_users 
 WHERE name IS NOT NULL
   AND name NOT IN (
   SELECT name 
     FROM users 
    WHERE name IS NOT NULL
       );

或者，同样的逻辑:

   SELECT su.name 
     FROM staging_users su
LEFT JOIN users u
       ON u.name = su.name
      AND su.name IS NOT NULL
      AND u.name IS NOT NULL;

作为额外说明，我会严重质疑允许用户使用 NULL 名称的数据结构。如果更改此设置，您的原始查询将有效。