PostgreSQL中的“NOT IN”和子查询

Question

我想执行这个查询：

SELECT mac, creation_date 
FROM logs 
WHERE logs_type_id=11
AND mac NOT IN (select consols.mac from consols)

但是我没有得到任何结果。我测试过了，我知道语法有问题。在MySQL中这样的查询完全没问题。我添加了一行以确保consols表中不存在一个名为mac的记录，但仍然没有返回任何结果。

- kskaradzinski

---

“consols.mac”列是“NULL”还是“NOT NULL”？ - Mark Byers

Answer 1

在使用 NOT IN 时，您应确保没有任何一个值为 NULL：

SELECT mac, creation_date 
FROM logs 
WHERE logs_type_id=11
AND mac NOT IN (
    SELECT mac
    FROM consols
    WHERE mac IS NOT NULL -- add this
)

- Mark Byers

---

注意：子查询中的 WHERE mac IS NOT NULL 子句是不必要的，因为 In(...) 函数总是删除 NULL（和重复项）。因为集合不能包含 NULL。 - wildplasser

@wildplasser 我不太确定。在我加上 IS NOT NULL 之前，它对我来说是无效的。嵌套的 SELECT 返回了一些 NULLS，这导致了 IN(SELECT...) 的错误。 - robins35

我非常希望能够解释一下为什么“IS NOT NULL”会导致这个程序正常工作。 - mbarkhau

似乎在 NOT IN 子句中使用 NULL 是无效的，因为与 NULL 进行比较既不为真也不为假。http://www.sqlbadpractices.com/using-not-in-operator-with-null-values/ - mbarkhau

我也注意到了一个小组的声音，即在没有“is not null”的情况下，查询不返回任何行，但是当添加“is not null”时，我获得了所需的值。非常棒的答案。 - Ray Toal

如果子查询没有匹配的值并且至少有一个null值，则在缺少is not null的情况下，查询将不返回任何行。来自当前（版本10）PostgreSQL手册的第9.22节：“[…]如果没有相等的右侧值且至少有一行右侧产生了空值，则NOT IN构造的结果将为null，而非true。” - Christopher Lewis

Answer 2

在使用NOT IN时，您还应考虑使用NOT EXISTS，它会悄无声息地处理空值情况。另请参见PostgreSQL Wiki

SELECT mac, creation_date 
FROM logs lo
WHERE logs_type_id=11
AND NOT EXISTS (
  SELECT *
  FROM consols nx
  WHERE nx.mac = lo.mac
  );

- wildplasser

---

还要注意，使用“NOT EXISTS”与“... NOT IN”相比会有巨大的性能损失。 - IcanDivideBy0

@IcanDivideBy0 在大多数情况下，它们生成相同的查询计划。你测试过吗？ - wildplasser

在子查询的情况下，使用 NOT IN 而不是 NOT EXISTS 也会带来性能上的提升。请参阅 https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_NOT_IN。 - Gerbrand

Answer 3

你也可以使用LEFT JOIN和IS NULL条件：

SELECT 
  mac, 
  creation_date 
FROM 
  logs
    LEFT JOIN consols ON logs.mac = consols.mac
WHERE 
  logs_type_id=11
AND
  consols.mac IS NULL;

在“mac”列上建立索引可能会提高性能。

- Frank Heikens