使用连接在 PostgreSQL 中合并来自不同表的數據

内连接

默认连接称为内连接。在 PostgreSQL 中，这可以使用 INNER JOIN 或只是简单的 JOIN 来指定。

这是一个演示内连接语法的典型示例

SELECT
    *
FROM
    table_1
[INNER] JOIN table_2
    ON table_1.id = table_2.table_1_id;

内连接是最严格的连接类型，因为它只显示通过组合两个表中的行创建的行。任何在组成表中没有在另一个表中具有匹配对应项的行都会从结果中删除。例如，如果第一个表在比较列中具有值为“blue”的值，而第二个表没有记录具有该值，则该行将从输出中屏蔽。

如果将结果表示为组成表的 Venn 图，则内连接允许你表示两个圆圈的重叠区域。只有在一个表中存在的任何值都不会显示。

左连接

左连接 是一种显示内连接中找到的所有记录，加上第一个表中的所有不匹配行的连接。在 PostgreSQL 中，这可以指定为 LEFT OUTER JOIN 或只是 LEFT JOIN。

左连接的基本语法遵循以下模式

SELECT
    *
FROM
    table_1
LEFT JOIN table_2
    ON table_1.id = table_2.table_1_id;

左连接通过首先执行内连接来构造两个表中的所有匹配记录的行。之后，第一个表中的不匹配记录也将被包含在内。由于每个连接中的 row 都包含两个表的列，因此不匹配的列使用 NULL 作为第二个表中所有列的值。

如果将结果表示为组成表的 Venn 图，则左连接允许你表示整个左侧圆圈。由两个圆圈之间的交集表示的左侧圆圈的部分将具有由右侧表补充的额外数据。

右连接

一个 右连接 是一个连接，它显示在内部连接中找到的所有记录，以及来自第二个表的所有不匹配行。在 PostgreSQL 中，这可以指定为 RIGHT OUTER JOIN 或者只是 RIGHT JOIN。

右连接的基本语法遵循以下模式

SELECT
    *
FROM
    table_1
RIGHT JOIN table_2
    ON table_1.id = table_2.table_1_id;

右连接通过首先执行内部连接来构建来自两个表中所有匹配记录的行来构建。之后，还包括来自第二个表的未匹配记录。由于连接中的每一行都包含两个表的列，因此未匹配的列使用 NULL 作为第一个表中所有列的值。

如果你将结果表示为组成表的维恩图，那么右连接可以让你表示整个右圆圈。右圆圈中由两个圆圈之间的交集表示的部分将从左表中补充额外的。数据。

全连接

一个 全连接 是一个连接，它显示在内部连接中找到的所有记录，以及来自两个组成表的所有不匹配行。在 PostgreSQL 中，这可以指定为 FULL OUTER JOIN 或者只是 FULL JOIN。

全连接的基本语法遵循以下模式

SELECT
    *
FROM
    table_1
FULL JOIN table_2
    ON table_1.id = table_2.table_1_id;

全连接通过首先执行内部连接来构建来自两个表中所有匹配记录的行来构建。之后，还包括来自两个表的未匹配记录。由于连接中的每一行都包含两个表的列，因此未匹配的列使用 NULL 作为未匹配其他表中所有列的值。

如果你将结果表示为组成表的维恩图，那么全连接可以让你完全表示两个组成圆圈。两个圆圈的交集将具有由每个组成表提供的。值。圆圈中不在重叠区域的部分将具有它们所属表的。值，使用 NULL 来填充在另一个表中找到的列。

交叉连接

还提供了一种特殊的连接，称为 CROSS JOIN。交叉连接不使用任何比较来确定每个表中的行是否匹配。相反，结果是通过简单地将第一个表中的每一行添加到第二个表中的每一行来构建的。

这会产生两个或多个表的行的笛卡尔积。实际上，这种连接方式无条件地组合了来自每个表的行。因此，如果每个表都有三行，则结果表将有九行，其中包含来自两个表的。所有列。

例如，如果你有一个名为 t1 的表与一个名为 t2 的表结合，每个表都有行 r1、r2 和 r3，则结果将是九行，如下所示。

t1.r1 + t2.r1
t1.r1 + t2.r2
t1.r1 + t2.r3
t1.r2 + t2.r1
t1.r2 + t2.r2
t1.r2 + t2.r3
t1.r3 + t2.r1
t1.r3 + t2.r2
t1.r3 + t2.r3

自连接

自连接是任何将表的行与自身结合的连接。可能不清楚这如何有用，但它实际上有许多常见的应用。

通常，表描述可以在彼此的关系中扮演多种角色的实体。例如，如果你有一个 people 表，则每一行都可能包含一个 mother 列，该列引用表中的其他 people。自连接将允许你通过将第二个表的实例连接到第一个表的实例来将这些不同的行缝合在一起，其中这些值匹配。

由于自连接两次引用同一个表，因此需要表别名来消除歧义引用。例如，在上面的示例中，你可以使用别名 people AS children 和 people AS mothers 连接 people 表的两个实例。这样，你就可以在定义连接条件时指定要引用的表的哪个实例。

这是另一个示例，这次表示员工和经理之间的关系

SELECT
    *
FROM
    people AS employee
JOIN people AS manager
    ON employee.manager_id = manager.id;

ON 子句

定义表连接条件的最标准方法是使用 ON 子句。 ON 子句使用等号来指定将比较的每个表中的精确列，以确定何时可能发生连接。PostgreSQL 使用提供的列将来自每个表的行缝合在一起。

ON 子句是最详细的，但也是所有可用连接条件中最灵活的。它允许在组合的每个表的列名如何标准化方面提供特异性。

ON 子句的基本语法如下所示

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.ident;

在这里，来自 table1 和 table2 的行将在 table1 中的 id 列与 table2 中的 ident 列匹配时连接起来。由于使用了内部连接，因此结果将只显示已连接的行。由于查询使用了通配符 * 字符，因此将显示来自两个表的. 所有列。

这意味着将显示 table1 中的 id 列和 table2 中的 ident 列，即使它们具有完全相同的值，因为它们满足了连接条件。你可以通过在 SELECT 列列表中调用要显示的精确列来避免这种重复。

USING 子句

USING 子句是指定 ON 子句条件的简写，当要比较的列在两个表中具有相同名称时可以使用该子句。 USING 子句采用一个列表（括在括号中），其中包含应该比较的共享列名。

USING 子句的一般语法使用以下格式

SELECT
    *
FROM
    table1
JOIN
    table2
USING
    (id, state);

此连接在两个表都共享的两个列（id 和 state）都具有匹配值时，将 table1 与 table2 结合起来。

此相同的连接可以使用 ON 更详细地表达，如下所示

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.id AND table1.state = table2.state;

虽然以上两个连接都会产生相同的行，并具有相同的数据，但它们会以略微不同的方式显示。虽然 ON 子句包含来自两个表的所有列，但 USING 子句会抑制重复列。因此，而不是有两个单独的 id 列和两个单独的 state 列（一个用于每个表），结果将只有一个 id 列和一个 state 列，后面跟着由 table1 和 table2 提供的所有其他列。

NATURAL 子句

NATURAL 子句是另一个简写，可以进一步减少 USING 子句的冗长。 NATURAL 连接不会指定任何要匹配的列。相反，PostgreSQL 将自动基于每个数据库中具有匹配列的所有列来连接表。

NATURAL 连接子句的一般语法如下所示

SELECT
    *
FROM
    table1
NATURAL JOIN
    table2;

假设 table1 和 table2 都有名为 id、state 和 company 的列，则上面的查询将等效于使用 ON 子句的以下查询

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.id AND table1.state = table2.state AND table1.company = table2.company;

以及使用 USING 子句的以下查询

SELECT
    *
FROM
    table1
JOIN
    table2
USING
    (id, state, company);

与 USING 子句类似， NATURAL 子句会抑制重复列，因此结果中只会存在每个连接列的一个实例。

虽然 NATURAL 子句可以减少查询的冗长，但在使用时必须谨慎。由于用于连接表的列是自动计算的，如果组件表中的列发生变化，结果可能会有很大差异，因为新的连接条件会导致不同的结果。