迹忆客 专注技术分享

NOT IN 中的 NOT 反转了简单使用 IN 运算符的结果。NOT IN 运算符的右侧必须有一个子查询，其中返回多个列以检查表达式是否与数据匹配。

如果在返回的子查询数据中找不到表达式，则 NOT IN 倾向于返回 true。

让我们继续尝试了解在 PostgreSQL 中使用标准 SQL NOT IN 时用户面临的一些常见问题和问题。

在 PostgreSQL 中将 NOT IN 运算符与子查询一起使用

在 PostgreSQL 中，如果你使用 NOT IN 来确保没有任何表达式匹配特定的数据集，则必须确保返回的子查询数据中没有 NULL 值。

这到底是什么意思？

让我们继续尝试借助示例来理解这一点。我们将创建一个包含两列的表 HORSE； ID 和 颜色。

CREATE TABLE horse (
    ID int PRIMARY KEY,
    Colour TEXT
);

现在让我们也插入一些值。

INSERT INTO horse values (1, 'black'), (2, 'brown'), (3, 'white');

让我们继续为 RIDER 创建另一个表。

CREATE TABLE rider (
    ID int PRIMARY KEY,
    horse_id int
);

你可以在 PGADMIN 和 PSQL 中运行上面给出的任何代码。

让我们在两个表中插入一些值：

INSERT INTO horse values (1, 'black'), (2, 'brown'), (3, 'white');

INSERT INTO rider values (1, 1), (2, 2), (3, 4)

在这里，你可以看到 RIDER 中的 id 3 有马 4，这在 HORSE 表中不存在。我们这样做是为了确保在我们的示例中使用 NOT IN。

假设我们想从 RIDER 表中删除这个 RIDER 3。我们如何做到这一点？

select * from rider
where horse_id not in (select id from horse)

输出：

现在，如果在 HORSE 表中我们也有一些空值，而不是干净的值，该怎么办。让我们修改 HORSE 表的 INSERT 语句。

INSERT INTO horse values (1, 'black'), (2, 'brown'), (3, 'white'), (NULL, NULL);

附带说明，当在主键列中插入 NULL 值时，请从表中删除主键约束以允许插入 NULL。

在这里，我们的 NULL HORSE 有一个 NULL Color，所以当我们像上面那样运行 NOT IN 的查询时，我们得到以下信息：

输出：

那么刚刚发生了什么？它不应该返回 ID 4，因为 HORSE 表中不存在？

好吧，让我们了解一下 NOT IN 是如何工作的。NOT IN 运算符使用 AND 运算符。如果要搜索的所有行都返回 true，它将返回 true。

所以像这样的东西可以代替 NOT IN：

NOT IN (ROW 1) AND NOT IN (ROW 2) AND NOT IN (ROW 3) .....

在我们的例子中，前三个数据集的 NOT IN 返回 true，它不会为 NULL 列返回任何值，因为 PostgreSQL 文档引用：

If all the per-row results are either unequal or null, with at least one null, then the result of `NOT IN` is null

从 NOT IN 返回 NULL 会导致所有其他 true 为 false，因此我们的表不返回任何内容。

我们如何解决这个问题？

第一种方法是防止向表中插入任何 NULL。尽管如此，如果我们已经在数据库中创建了表并且现在想要运行查询来获取数据，那么这也是没有用的。

因此，我们必须寻找其他解决方案来有效地解决这个问题。

使用 NOT EXISTS 运算符作为更好的选择

让我们使用以下查询：

select horse_id from rider r
where not exists
(select id from horse h
where h.id = r.horse_id)

即使存在空值，这也倾向于最终将值 4 返回给我们，并且是一种有效的策略。让我们看看它是如何工作的。

如果子查询返回任何内容，即任何单行，则 EXISTS 子句返回 true，否则返回 false。因此，当我们想要找到丢失的 HORSE 时，我们倾向于从 HORSE 表中返回与 RIDER 表中的 IDs 相等的值。

子查询返回多行，并且 EXISTS 变为真，使 NOT EXISTS FALSE。

最终，我们的最终查询从 RIDER 中选择 HORSE_ID，它不等于 HORSE 表中提供的 IDs。在我们的例子中，即 4，因此我们的查询完美运行。

但是，NOT EXISTS 在用于 NOT IN 运算符时会导致性能损失。

select horse_id from rider
full join horse on rider.horse_id = horse.ID
where horse.ID is null

输出：

所以它返回一个 NULL 和我们没有找到的值； 4。如何？

当我们在两个 ID 相似的条件下进行完全连接时，它也倾向于返回不相似的行。它将返回来自 HORSE 的集合 (NULL, NULL) 和来自 RIDER 的 (3, 4)，因为它们都是不匹配的。

因此，我们可以利用这一点并在最后写入 NULL 条件以返回这些不匹配的行。

当我们写下 Horse.ID is NULL 时，它将从为 NULL 的骑手中选择 HORSE_ID。在这种情况下，第一个集合 (NULL, NULL) 被包括在内；集合 (3,4) 也是如此。为什么？

该集合不包含 NULL，但也是不匹配的。所以我们的 FULL JOIN 在它的返回表中也设置了 NULL 值。

因此，我们得到这个值作为结果输出给我们。

今天我们研究了为包含 NULL 的值实现 NOT IN 运算符。我们希望这对你有所帮助并扩展你的知识库。