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

内连接

默认的连接称为内连接（inner join）。在 PostgreSQL 中，这可以通过使用INNER JOIN或简单地使用JOIN来指定。

下面是一个演示内连接语法的典型示例：

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

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

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

左连接

左连接是一种连接，它显示内连接中找到的所有记录，以及来自第一个表的所有不匹配行。在 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子句可以减少查询的冗长，但在使用它时必须小心。由于用于连接表的列是自动计算的，如果组件表中的列发生变化，结果可能会因新的连接条件而大相径庭。