在 MySQL 中使用连接（Join）合并不同表的数据

内连接和交叉连接

默认的连接类型称为内连接（inner join）。在 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

左连接

左连接（left join）是一种显示内连接中所有记录，外加第一个表中所有不匹配行的连接。在 MySQL 中，这可以指定为 LEFT OUTER JOIN 或仅 LEFT JOIN。

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

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

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

如果您将结果表示为组成表的维恩图，左连接允许您表示整个左圆。左圆中由两个圆交集表示的部分将包含由右表补充的额外数据。

右连接

右连接（right join）是一种显示内连接中所有记录，外加第二个表中所有不匹配行的连接。在 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 建议您尽可能使用左连接而不是右连接。

全连接

全连接（full join）是一种显示内连接中所有记录，外加两个组成表中所有不匹配行的连接。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 中的 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 连接不指定任何要匹配的列。相反，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 子句可以减少查询的冗长，但在使用时必须小心。因为用于连接表的列是自动计算的，如果组成表中的列发生变化，由于新的连接条件，结果可能会大相径庭。