使用连接组合来自 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”，而第二个表中没有值为“blue”的记录，则该行将从输出中抑制。

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

如上所述，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 作为第二个表中所有列的值。

如果将结果表示为组件表的 Venn 图，则左连接允许您表示整个左圆。由两个圆之间的交集表示的左圆部分将具有由右表补充的附加数据。

右连接

右连接是一种显示内连接中找到的所有记录，以及来自第二个表的所有不匹配行的连接。在 MySQL 中，可以将其指定为 RIGHT OUTER JOIN 或仅指定为 RIGHT JOIN。

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

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

右连接的构造方式是：首先执行内连接，以从两个表中的所有匹配记录构造行。之后，还包括来自第二个表的不匹配记录。由于连接中的每一行都包含两个表的列，因此不匹配的列使用 NULL 作为第一个表中所有列的值。

如果将结果表示为组件表的 Venn 图，则右连接允许您表示整个右圆。由两个圆之间的交集表示的右圆部分将具有由左表补充的附加数据。

出于可移植性原因，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 作为不匹配的另一个表中所有列的值。

如果将结果表示为组件表的 Venn 图，则全连接允许您完整地表示两个组件圆。两个圆的交集将具有由每个组件表提供的值。重叠区域外部的圆部分将具有来自它们所属表的值，并使用 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 子句可以减少查询的冗长性，但在使用它时必须谨慎。由于用于连接表的列是自动计算的，因此如果组件表中的列发生更改，则由于新的连接条件，结果可能会大相径庭。