在 PostgreSQL 中使用 JOIN 连接不同表的数据

内连接

默认的连接称为内连接。在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表，每行都可能包含一个引用表中其他people的mother列。自连接将允许您通过将表的第二个实例与第一个实例连接起来，其中这些值匹配，从而将这些不同的行拼接在一起。

由于自连接两次引用同一个表，因此需要表别名来消除引用歧义。例如，在上面的示例中，您可以使用别名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子句的冗余。自然连接不指定任何要匹配的列。相反，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子句可以减少查询的冗长，但在使用时必须谨慎。由于用于连接表的列是自动计算的，如果组件表中的列发生更改，由于新的连接条件，结果可能会大相径庭。