使用连接在 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;

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

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

全连接

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

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

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

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

如果将结果表示为组成表的 Venn 图，则全连接允许您完全表示两个组成圆。两个圆的交集将具有由每个组成表提供的值。圆的重叠区域外部的部分将具有来自它们所属表的值，并使用 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 表的两个实例与别名 people AS children 和 people AS mothers 连接起来。这样，您可以在定义连接条件时指定您引用的表的哪个实例。

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

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 子句可以减少查询的冗长性，但在使用它时必须谨慎。由于用于连接表的列是自动计算的，因此如果组件表中的列发生更改，则由于新的连接条件，结果可能会大相径庭。