SQL 干货 | SQL 反连接

最强大的 SQL 功能之一是 JOIN 操作，它提供了一种优雅而简单的方法，将一个表中的每一条记录与另一个表中的每一条记录结合起来。不过，有时我们可能想从一个表中找到另一个表中没有的值。正如我们将在今天的博客文章中看到的，通过包含一个谓词来连接表，连接也可以用于此目的。这种连接被称为反连接，对于回答各种与业务相关的问题很有帮助，例如：

• 哪些客户没有下单？
• 哪些员工还没有被分配到部门？
• 本周哪些销售人员没有达成交易？

本期博客将以 PostgreSQL dvdrental 数据库 为例，介绍反连接的类型以及如何编写反连接。 我们将在 Navicat Premium Lite 17 中编写和执行查询。

反连接的两种类型

这是两种类型的反连接：

• 左反连接：返回左表中没有匹配右表记录的记录
• 右反连接：返回右表中与左表中不匹配的记录

下图中 蓝色 显示部分表示返回的行：

下一节将以左反连接为例，介绍我们可以用来创建反连接的几种不同语法。

使用 EXISTS 的左反连接

比方说，我们想在 dvdrental 数据库中找到所有没有出现在任何电影中的演员。遗憾的是，SQL 没有这种操作的内置语法，但我们可以使用 EXISTS，或更具体地说，使用 NOT EXISTS 来模拟这种操作。下面是这种查询的结果：

SELECT *
FROM actor a
WHERE NOT EXISTS (SELECT * FROM film_actor faWHERE a.actor_id = fa.actor_id
)

若我们在 Navicat Premium Lite 17 中运行它，我们会获得下面的结果：

请注意 NOT IN!

既然 EXISTS 和 IN 是等价的，你可能会得出结论，NOT EXISTS 和 NOT IN 也是等价的，但事实并非总是如此！只有当右表（本例中为 film_actor）的外键（actor_id）上有 NOT NULL 约束时，它们才是等价的。

在这个特定示例中，由于 actor_id 列上的 NOT NULL 约束，NOT IN 查询会返回相同的结果：

如果 actor_id 列允许空值，则将返回空结果集。我们可以通过下面的查询来验证这一点：

SELECT *
FROM actor
WHERE actor_id NOT IN (1, 2, 3, 4, 5, NULL)

上述查询没有返回任何行，因为在 SQL 中，NULL 代表一个未知值。由于我们无法确定 actor_id 是否在一个值集中（其中一个值是未知的），因此整个谓词（predicate）就变成了未知（UNKNOWN）！

要避免 NOT IN 语法带来的危险，最简单的方法就是坚持使用 NOT EXISTS。因为 DBA 可能会暂时关闭该约束以加载一些数据，从而使你的查询在此期间毫无用处。

替代语法

正如介绍中提到的，也可以使用左连接和右连接执行反连接。要做到这一点，需要添加一个带有 IS NULL 谓词的 WHERE 子句。下面是该语法的 LEFT JOIN 版本：

SELECT a.*
FROM actor aLEFT JOIN film_actor faON a.actor_id = fa.actor_id
WHERE fa.actor_id IS NULL

请注意，左/右连接语法的运行速度可能会更慢，因为查询优化器不会将其识别为反连接操作。

结语

在今天的博客中，我们学习了如何使用三种不同的 SQL 语法来模拟左反连接。其中，NOT EXISTS 应该是首选，因为它能最好地传达反连接的意图，而且执行速度最快。

有兴趣试用 Navicat Premium Lite 17 吗？你可以下载它进行 为期 14 天的全功能免费试用 。 它适用于 Windows、macOS 和 Linux 操作系统。

Navicat 17 最新资讯 & 技术干货

- Navicat 17 体验官火热招募中

- Navicat- 17 新特性 | 用户界面再升级

- Navicat 17 新特性 | 模型设计创新与优化

- Navicat 17 新特性 | 查询与配置

- Navicat 17 新特性 | Navicat BI 功能革新升级

- Navicat 17 新特性 | 原生支持国产 Linux ARM 平台以及银河麒麟与统信操作系统

- 聚焦 Navicat 17 新特性 | 数据字典提升数据结构清晰度

- Navicat 17 新增 PolarDB 与 Garnet 数据库

- Navicat 17 新特性 | 聚焦 MongoDB

- Navicat 17 新特性 | 新增 Redis 哨兵部署模式

- 免费版 Navicat Premium Lite