使用联接将来自不同表的数据合并到 MySQL 中

内部联接和交叉联接

默认联接称为内部联接。在 MySQL 中，可以使用 INNER JOIN、仅使用 JOIN 或使用 CROSS JOIN 来指定此联接。对于其他数据库系统，INNER JOIN 和 CROSS JOIN 通常是两个不同的概念，但 MySQL 在同一个结构中实现它们。

以下是一个演示内部联接语法的典型示例

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

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

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

如上所述，MySQL 还使用此格式来生成交叉联接。在 MySQL 中，您可以使用没有匹配条件的内部联接来生成交叉联接。交叉联接不使用任何比较来确定每个表中的行是否匹配。相反，结果是通过简单地将第一个表中的每一行添加到第二个表中的每一行来构建的。

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

例如，如果您有一个名为 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

左联接

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

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

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

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

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

右联接

右联接是一种联接，它显示在内联接中找到的所有记录，加上第二个表中所有未匹配的行。在 MySQL 中，这可以指定为 RIGHT OUTER JOIN 或 RIGHT JOIN。

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

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

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

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

出于可移植性的原因，MySQL 建议您尽可能使用左联接而不是右联接。

全联接

全联接是一种联接，它显示在内联接中找到的所有记录，加上两个组件表中所有未匹配的行。MySQL 本身没有实现全联接，但我们可以使用一些技巧来模拟这种行为。

要复制全外联接的结果，我们将执行左联接以获取两个表共享的所有结果和左表中的所有未匹配行。然后，我们将使用 UNION ALL 集操作符 将这些结果与右表的“反联接”组合在一起。“反联接”是一种联接操作，它专门查找表之间不共有的结果。

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

(   SELECT
        *
    FROM
        table_1
    LEFT JOIN table_2
        ON table_1.id = table_2.table_1_id
)
UNION ALL
(   SELECT
        *
    FROM
        table_1
    RIGHT JOIN table_2
        ON table_1.id = table_2.table_1_id
    WHERE table_1.id IS NULL
);

由于联接中的每一行都包含两个表的列，因此未匹配的列使用 NULL 作为未匹配的其他表中所有列的值。

如果您将结果表示为组件表的维恩图，则全联接允许您完全表示两个组件圆。两个圆的交集将具有每个组件表提供的值。圆形外部区域部分将具有其所属表的相应值，使用 NULL 填充其他表中找到的列。

自联接

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

通常，表描述实体，这些实体可以在彼此的关系中扮演多种角色。例如，如果您有一个 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 子句使用等号来指定每个表中将被比较的精确列，以确定何时可能发生联接。MySQL 使用提供的列将来自每个表的行缝合在一起。

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 联接不指定任何要匹配的列。相反，MySQL 将根据每个数据库中具有匹配列的所有列自动联接表。

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