跳至主要内容

SQL 连接（Join）入门

在本教程中，您将了解 SQL 中连接的机制及其不同类型。
· 9分钟 阅读

使用 AI 探索

在使用 SQL 进行数据分析时，您经常需要查看多个表，并将它们连接起来以获得所需结果。对数据进行联表分析也是数据科学家的必备技能。在本教程中，您将学习在 PostgreSQL 中可以执行的不同类型的连接。您将先学习 SQL 连接的基础概念，然后了解不同类型连接及其在 PostgreSQL 中的语法。最后，您将使用两个表执行各种连接查询。为便于跟进，您应熟悉 SQL 基础以及如何在 PostgreSQL 中编写简单查询。如果您正在寻找学习这些内容的资源，以下可能会有帮助：

我们开始吧。

要点速览

  • SQL 连接可通过公共列将两个或多个表中的数据合并。

  • INNER JOIN 仅返回匹配的行，而 OUTER JOIN 的变体（LEFT、RIGHT、FULL）会保留一侧或两侧未匹配的行，并用 NULL 填充缺口。

  • CROSS JOIN 会产生每一种可能的行组合（笛卡尔积），而 SELF JOIN 允许将表与其自身连接——这对于在同一数据集中比较行非常有用。

  • 半连接（semi-join）和反连接（anti-join）通常写作带有 IN 或 NOT IN 的子查询，根据是否满足另一张表中的条件来筛选一张表的行。

在 PostgreSQL 中搭建数据库环境

在学习 SQL 连接的基础之前，最好先创建一些用于分析的表。您至少需要两张表，并且至少有一列是共有的。虽然您可以在同一张表上执行自连接查询，但我们暂且不考虑这种情况。

您可以在一个名为 pgAdmin 的工具中执行所有 SQL 操作，它通常会随 PostgreSQL 一起安装。打开 pgAdmin 并创建一个数据库（名称可自定）以便创建表，如果您没有可用的现有数据库。然后可按以下规格创建两张表：

  • student_name (id, name)
  • student_stream (id, stream)

注意，两张表都有共同的 id 列。您可以使用以下 CREATE 语句来创建这些表：

  • student_name 表 - 
    CREATE TABLE student_name
(
id smallint,
name "char"[]
);
  • student_stream 表 - 
    CREATE TABLE student_stream
(
id smallint,
stream "char"[]
);

现在让我们向表中插入一些记录。

  • student_name 表 -
INSERT INTO student_name(id, name) VALUES (1, 'Sayak');

INSERT INTO student_name(id, name) VALUES (2, 'Alex');

INSERT INTO student_name(id, name) VALUES (3, 'Sameer');

INSERT INTO student_name(id, name) VALUES (4, 'Rick');
  • student_stream 表 -
INSERT INTO student_stream(id, stream) VALUES (1, 'CS');

INSERT INTO student_stream(id, stream) VALUES (1, 'IT');

INSERT INTO student_stream(id, stream) VALUES (2, 'ECE');

INSERT INTO student_stream(id, stream) VALUES (9, 'ECE');

如果您按上述查询原样执行，表中的条目应如下图所示：

student_name 和 student_stream 表

现在您已经准备好两张简单的表了。接下来我们来学习 SQL 连接的基础。

SQL 连接——基础

SQL 连接允许您使用公共标识符将两张或多张（有时甚至是一张）表合并。以您创建的上述两张表为例——它们都有共同的 id 列。您可能会质疑在 SQL 中进行连接的必要性。我们简要讨论一下。

由于规范化约束，您可能无法在单个表中获得所有所需信息。规范化不仅是期望的，也是必要的，它有助于保持一致性、减少冗余并防止插入和更新异常。再看上面的两张表。假设您想找出 Sayak 报名了哪些专业（stream）。要回答这个问题，您需要将两张表连接起来（也可以使用子查询，但我们暂不考虑）。

要连接两张表，它们之间必须有共同点。这是否意味着两张表至少要有一列同名？或者具体条件是什么？

您希望连接的两张表不一定要有同名列，但逻辑上应是相同的，即它们的数据类型应相同。您不能连接两张虽同名但数据类型不同的列。下面我们来学习不同类型的 SQL 连接。

SQL 连接的不同类型

本节将学习多种 SQL 连接类型：

  • INNER JOIN
    • 自连接（Self-join）
  • OUTER JOIN
    • LEFT JOIN
    • RIGHT JOIN
    • FULL JOIN
  • CROSS JOIN
  • 半连接与反连接

PostgreSQL 为上述大写的连接类型提供了对应的关键字。我们逐一学习。为此，您将先用可视化方式理解连接，然后在 PostgreSQL 中执行相应的连接查询。

INNER JOIN

先看下图以直观理解 INNER JOIN：

内连接示意图

在上述示例中，我们关注的列是 id。INNER JOIN 会忽略两张表中不相同的其他列取值。现在在 PostgreSQL 中执行一个在 student_name 与 student_stream 两表之间进行 INNER JOIN 的查询。

执行以下查询后，您将得到与上图一致的结果：

SELECT s1.id, s1.name, s2.stream
FROM student_name AS s1
INNER JOIN student_stream AS s2
ON s1.id = s2.id;

s1 和 s2 是两张表的别名。您在 INNER JOIN 中使用了 ON 关键字。也可以使用 USING 关键字来执行该查询：

SELECT s1.id, s1.name, s2.stream
FROM student_name AS s1
INNER JOIN student_stream AS s2
USING (id);

自连接（Self Join） 允许您对同一张表进行连接。那么它适用于哪些场景？假设您有一张包含以下列的表：

  • Country
  • Continent

现在您想列出两两国家，且它们所属的洲相同。下图可帮助您理解预期结果：

自连接结果（表格形式）

OUTER JOIN

OUTER JOIN 可进一步分为三种类型：

LEFT JOIN 或 Left Outer JOIN：下图可帮助您直观理解 SQL 中的 LEFT JOIN：

SQL 左连接示意图

注意，与 INNER JOIN 不同，LEFT JOIN 会返回左表（相对于您在查询中指定的顺序）中在右表无匹配项的记录。由此可知，Sameer 和 Rick 尚未报名任何专业。对应的查询如下：

SELECT s1.id, s1.name, s2.stream
FROM student_name AS s1
LEFT JOIN student_stream AS s2
ON s1.id = s2.id;

RIGHT JOIN 或 Right Outer JOIN：RIGHT JOIN 与 LEFT JOIN 恰好相反：

SQL 右连接示意图

RIGHT JOIN 有助于找出没有任何学生报名的专业。其查询为：

SELECT s1.id, s1.name, s2.stream
FROM student_name AS s1
RIGHT JOIN student_stream AS s2
ON s1.id = s2.id;

FULL JOIN 或 Full Outer JOIN：FULL JOIN 将 LEFT JOIN 与 RIGHT JOIN 的结果合并在一起：

SQL 全外连接示意图

对应查询如下：

SELECT s1.id, s1.name, s2.stream
FROM student_name AS s1
FULL JOIN student_stream AS s2
ON s1.id = s2.id;

注意，以上所有 OUTER JOIN 查询也都可以使用 USING 关键字来执行。接下来学习 CROSS JOIN。

CROSS JOIN

CROSS JOIN 本质上是用 SQL 表达的两个元素之间的笛卡尔积。假如您需要获取两张表之间，甚至同一张表中所有可能的组合，就需要使用 CROSS JOIN。下图直观展示了这一概念：

交叉连接示意图

您已经有两张表可用于测试。若要获取 student_name 与 student_stream 两表的 id 列之间的所有可能组合，您可以执行以下查询：

SELECT s1.id, s2.id
FROM student_name AS s1
CROSS JOIN student_stream AS s2;

结果如下：

所有可能组合的表

下面来看另外两种 PostgreSQL 未提供直接关键字的连接类型。

半连接与反连接

回到您在本教程前面创建的表：

原始表

Semi Join 查询通常以子查询的形式执行，从第一张表中选出满足在第二张表中被匹配的某个条件（或条件集）的行。我们假定左表为第一张表，右表为第二张表。

Anti Join 查询则恰恰相反。在 Anti Join 中，从第一张表中选出在第二张表中不被匹配的条件（或条件集）对应的行。下图可帮助您直观理解：

半连接与反连接示意图

实现 Semi Join 的查询如下：

select id, name
from student_name
where id IN
(select id from student_stream where stream
IN ('CS', 'IT', 'ECE'));

类似地，实现 Anti Join 的查询在本例中如下：

select id, name
from student_name
where id NOT IN
(select id from student_stream where stream
IN ('CS', 'IT', 'ECE'));

注意上述查询中 NOT 的用法。

将您的 SQL 连接技巧更进一步

本教程到此结束。您学习了多种 SQL 连接类型，并熟悉了它们在 PostgreSQL 中的语法。如果您想练习一些更具挑战性的 SQL 连接练习，建议学习 DataCamp 的 Joining Data in SQL 课程。事实上，本文部分内容也参考了该课程材料。

如有问题，请在 Comments 区留言。

主题

SQL数据分析