主页 / dba / 问题 / 159137
Accepted
user113659
Asked: 2016-12-26 08:24:16 +0800 CST

NOT IN 行为的原因 [重复]

  • 772
sql> SELECT * FROM runners;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | John Doe     |
|  2 | Jane Doe     |
|  3 | Alice Jones  |
|  4 | Bobby Louis  |
|  5 | Lisa Romero  |
+----+--------------+

sql> SELECT * FROM races;
+----+----------------+-----------+
| id | event          | winner_id |
+----+----------------+-----------+
|  1 | 100 meter dash |  2        |
|  2 | 500 meter dash |  3        |
|  3 | cross-country  |  2        |
|  4 | triathalon     |  NULL     |
+----+----------------+-----------+

下面查询的结果是什么？

SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races);

上面的查询不会返回任何内容，因为 races 表包含 null winner_id。但这背后的原因是什么？

database-agnostic

2 个回答

  1. Best Answer
    SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races);

    是这样的（实际值来自races）：

    SELECT * FROM runners WHERE
id <> 2 and
id <> 3 and
id <> null;

    id <> null根据定义unknown，因此整个where子句的计算结果为false(for ids: 2, 3) 或unknown(for ids: 1, 4, 5) 因为and运算符，因此没有返回结果。

    如果要运行查询而不考虑 中的空值races，可以使用：

    SELECT * FROM runners 
WHERE id NOT IN 
      (SELECT winner_id FROM races WHERE winner_id IS NOT NULL);
    • 3

  2. 我建议不要做巴拉兹正在做的事情。这就是NOT EXISTS目的，它处理空值。让我们来看看这个..

    创建示例数据

    CREATE TABLE foo AS
SELECT * FROM generate_series(1,100) AS t(x);

CREATE TABLE bar AS
SELECT * FROM generate_series(1,10) AS t(x)
  UNION SELECT null;

    问题复习

    现在，根据较早的答案和文档，

    "NOT IN 等价于 <> ALL()`请注意，如果没有失败但至少右侧一行为运算符的结果产生 null，则 ALL 构造的结果将为 null，而不是 true。这是在符合 SQL 对空值布尔组合的常规规则。

    SELECT *
FROM foo
WHERE foo.x NOT IN ( SELECT x FROM bar );

    如果任何 bar.x 为 NULL，则整个条件为 NULL，因此返回 0 行。

    解决方案

    NOT IN如果集合可以为空并使用NOT EXISTS测试，最好的解决方案是完全跳过。

    SELECT *
FROM foo
WHERE NOT EXISTS (
  SELECT 1 FROM bar WHERE bar.x = foo.x
);

    这将返回 90 行。

    或者，这可以写成 this..

    SELECT *
FROM foo
LEFT OUTER JOIN bar
  ON foo.x = bar.x
WHERE bar.x IS NULL;
    • 1

相关问题