使用连接在 PostgreSQL 中合并来自不同表的数据

内连接

默认连接称为内连接。在 PostgreSQL 中，可以使用 INNER JOIN 或简写 JOIN 来指定。

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

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

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

如果将结果表示为组成表的维恩图，则内连接允许您表示两个圆的重叠区域。仅存在于其中一个表中的值都不会显示。

左连接

左连接 是一种连接，它显示内连接中找到的所有记录，以及来自第一个表的所有不匹配行。在 PostgreSQL 中，可以将其指定为 LEFT OUTER JOIN 或简写为 LEFT JOIN。

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

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

左连接的构造方式是首先执行内连接，以从两个表中的所有匹配记录构造行。之后，还包括来自第一个表的不匹配记录。由于连接中的每一行都包含两个表的列，因此不匹配的列使用 NULL 作为第二个表中所有列的值。

如果将结果表示为组成表的维恩图，则左连接允许您表示整个左圆。由两个圆之间的交集表示的左圆部分将具有由右表补充的附加数据。

右连接

右连接 是一种连接，它显示内连接中找到的所有记录，以及来自第二个表的所有不匹配行。在 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 中的 id 列与 table2 中的 ident 列匹配时，将连接来自 table1 和 table2 的行。由于使用了内连接，因此结果将仅显示已连接的行。由于查询使用了通配符 * 字符，因此将显示来自两个表的所有列。

这意味着将显示来自 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 列（每个表一个）），然后是 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 子句可以减少查询的冗长性，但在使用它时必须小心。由于用于连接表的列是自动计算的，因此如果组成表中的列发生更改，则由于新的连接条件，结果可能会大相径庭。