半连接、反连接和自然连接

Semi, Anti, and Natural Joins

我们都听说过 LEFT JOIN、RIGHT JOIN、INNER JOIN 和 OUTER JOIN，对吧？这四种是最常见的 SQL 连接类型。但其实还有更多。在这一章里，我想向你介绍其中三种我偶尔觉得颇为好用的连接——它们就是半连接（semi join）、反连接（anti join）和自然连接（natural join）。

半连接看起来和 left join 十分相似，但有三个显著的区别：

1. 如果两行之间的连接条件为 TRUE，则只返回左表的列。与之对比，left join 会返回两张表的列。

2. 如果左表中的某一行没有匹配项，则不会返回该行。而在 left join 中，无论是否匹配，左表中的所有行都会被返回。

3. 如果左表中的某一行有多个匹配项，只会返回一条记录。而在 left join 中，多个匹配项会按相应次数被返回。

例如，假设我们有以下两张表：

执行半连接后，我们得到以下结果：

如上所示，与 left join 不同：

• 它只返回左表的列。
• 它只返回左表中匹配到的行。

如果某条记录有多个匹配项，比如下面这种情况：

……那么我们会注意到，半连接只从左表返回一条记录：

我觉得当我只关心另一张表中是否存在某些记录时，半连接尤其有用。left join 会返回重复记录，而此时我们对这些重复记录并不感兴趣。

半连接从左表中丢弃的那些行，正是反连接的结果。所以从某种意义上说，我们可以认为：

再来看上面那张 orders 和 users 表（即没有多个匹配项的那张）：

执行反连接后，我们得到以下结果：

从半连接和反连接的结果可以清楚地看出：

当然，顺序可能不同。当我说“[SEMI JOIN] + [ANTI JOIN] = [LEFT TABLE]”时，我指的是所有记录的集合。我觉得当我想知道哪些记录在另一张表中不存在时，反连接尤其有用。

这种连接和 INNER JOIN 类似，但不需要显式指定连接条件。相反，它会自动把所有同名的列都作为连接条件。

再来看一次 users 和 orders 表：

这里，User_ID 列在两张表中都存在。执行自然连接（natural join）后，我们得到以下结果：

如上所示，结果与我们用 INNER JOIN 得到的相似。不过，我们不必显式指定 JOIN 条件，诚然，这既可能是好事也可能是坏事。

• 好的一面是，它有助于我们写出简洁的查询。
• 坏的一面是，我们并没有显式说明参与连接的列。

以上就是另外三种 SQL 连接，我有时会用它们来写出简洁优雅的 SQL 查询。如果你想动手实验我们讨论的内容，可以下载这个 Jupyter Notebook：https://bit.ly/4cRGMWg。