在 MySQL 中使用 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;

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

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

如上所述，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 中的 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 列），而不是有两个单独的 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 子句可以减少查询的冗余，但使用时必须谨慎。由于用于连接表的列是自动计算的，如果组件表中的列发生变化，结果可能会因新的连接条件而大相径庭。