如何在MySQL数据库中连接两个或者多个表 [附实例演示］

在数据库中管理数据时，将信息组织到单独的表中可以提高性能并保持一致性。但是，为了完成某些查询，往往需要将多个表中的数据合并起来。连接表可以根据共享字段值匹配记录，从而实现这一目的。

例如，将“customers”表与“orders”表连接起来，就可以在一次查询中检索订单和相应的客户信息。

不同类型的连接提供了各种组合表数据的方法。在本教学指南中，我们将学习如何在 MySQL 中连接两个或多个表、为什么这样做以及如何使实现。

什么是MySQL中的Joins？

简而言之，MySQL 连接可让您访问多个表中的数据。只要在 SQL 语句中连接两个或多个表，就会执行 MySQL 连接。

SQL 连接语句的基本格式如下：

SELECT
*
FROM
<first_table>
<join_type>
<second_table>
<join_condition>;

数据库连接（Database Joins）会根据不同数据源中特定列的匹配值，将记录合并在一起。每个结果行都包含来自第一张表和第二张表的数据，并根据共享的列值进行对齐。

在连接操作中，每一行都包含第一张表的所有列，接着是第二张表的所有列。你可以在查询的 SELECT 部分指定要显示哪些列。

MySQL的连接类型包括：

• MySQL内连接（Inner Join，亦称简单连接）
• MySQL左外连接（Left Outer Join，亦称左连接，它返回左表中的匹配记录）
• 右连接（Right Join，此连接返回右表中的匹配记录）
• 全连接（Full Join，此连接返回所有表中的匹配记录）

连接的类型以及指定的条件决定了每一行结果的构建方式，以及如何处理不满足连接条件的表中的行。

例如，内连接可以用于将“customers”表与“orders”表结合起来，从而检索订单及其对应的客户信息。

如何在MySQL中连接多个表？

如果您正在开发一个大型应用程序（如电子商务商店），并在其中创建了多个表（如客户、订单和产品），您可能需要连接表。

让我们来看一个例子，在这个例子中，我们要获取特定客户订购的所有产品。

让我们创建查询：

SELECT * FROM table1
LEFT JOIN table2 ON table2.id = table1.id
LEFT JOIN table3 ON table3.id = table1.id

如果查看上面的查询，我们首先连接表 1 和表 2，得到一个包含表 1 和表 2 合并数据的临时表，然后将其连接到表 3。这个等式可以扩展到多于 3 个表的 N 个表。您需要确保 SQL 查询有 N-1 个连接语句，以便连接 N 个表。

通过上述查询，可以将表 1（在任何情况下）的记录与其他两个表的记录进行匹配。使用 LEFT JOIN 可以将表 2 和表 3 与表 1连接起来（而不只是将表 2 与表 1 连接起来，将表 3 与表 2 连接起来）。

还可以添加WHERE、AND 和 ORDER BY 等条件：

SELECT * FROM table1
LEFT JOIN table2 ON table2.id = table1.id
LEFT JOIN table3 ON table3.id = table1.id
WHERE month = 'numberHere'
AND (table2.email IS NOT NULL OR table3.email IS NOT NULL)
ORDER BY submitdate DESC

在MySQL中可以连接多少个表？

根据 MySQL 8.0 的官方文档，JOIN 语句中表的最大数量是 61。不过，请注意，随着表数量的增加，JOIN 语句可能会占用大量服务器资源。如果您的查询属于这种情况，我强烈建议您将其分解为多个查询，以减少服务器负载。

MySQL中的连接类型

连接有不同的类型，每种类型都能产生不同的数据集。了解它们的工作原理可以帮助您选择最合适的连接，以实现查询所需的数据结果。

• 内连接（Inner Join）
• 右连接（Right Join）
• 左连接（Left Join）
• 并集（Union）
• 交叉连接或笛卡尔积（Cross Join）
• 自连接（Self Join）
• 全外连接（Full Outer Join）
• 自然连接（Natural Join）

为了演示每种连接类型的行为，让我们考虑有两个表的情况：myguests 表和 messages 表。

messages 表的模式

我们将创建一个名为“messages”的表来存储消息和发送者的用户 ID。下面是我们的表的模式：

CREATE TABLE `messages` (
`id` int(11) NOT NULL,
`message` varchar(255) NOT NULL
);

该表将作为我们测试每种连接类型的参考。

内连接

在 MySQL 中，默认的连接类型是内部连接。可以使用 INNER JOIN 或 JOIN 来指定。此外，MySQL 还支持 CROSS JOIN。在某些数据库系统中，INNER JOIN 和 CROSS JOIN 是两个独立的概念，而 MySQL 则不同，它将这两个概念合并为一个结构。

Inner Join 查询的结构如下：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

Inner Join 和 simple Join 是一样的。您也可以这样编写查询：

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

现在，让我们使用内部连接从数据库中获取姓名和信息。查询结果如下：

$sql = "SELECT CONCAT(myguests.firstname,' ',myguests.lastname) AS name, myguests.email, messages.message 
From myguests 
INNER JOIN messages 
ON myguests.id = messages.id";

在 MySQL 中，CONCAT 函数用于连接两个字符串列。现在打开我们之前创建的 index.php，复制以下代码。

<table>
    <tr>
        <td>   Name</td>
        <td>   Email</td>
        <td>   Message</td>
        <!-- <td>   Message</td>
        <td>   Date</td>!-->
    </tr>
<?php
include 'crud.php';
$sql = "SELECT CONCAT(myguests.firstname,' ',myguests.lastname) AS name, myguests.email, messages.message 
        From myguests 
        INNER JOIN messages 
        ON myguests.id = messages.id";
$result = selectdata($sql);
    if($result != "zero")
    {
        while($row = $result->fetch_assoc())
        {
            echo "<tr>";
            echo "<td>" . $row['name'] . "</td>";
            echo "<td>" . $row['email'] . "</td>";
            if($row['message'] === null){echo "<td>" .  'null'. "</td>";} else { echo "<td>" .  $row['message']. "</td>"; } ;
            echo "</tr>";
        }
    }
    else
        {
            echo $result;
    }
?>
</table>
?>

在 PHP 主机服务器上运行该页面时，结果将如下所示：

可以清楚地看到，它只返回了符合user_id且消息不为空的结果。

右连接

RIGHT JOIN 将表结合起来，返回右表的所有值和左表的匹配值。当没有找到匹配值时，它还包括左表中的 NULL 值。

RIGHT JOIN 的结构是：

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

假设我们要从数据库中获取姓名和消息，右表为消息，左表为 myguests。查询将是：

$sql = "SELECT CONCAT(myguests.firstname,' ',myguests.lastname) AS name, myguests.email, messages.message 
From myguests 
RIGHT JOIN messages 
ON messages.id = myguests.id";

该查询会返回 messages 表中的所有消息。对于每条消息，它还会从myguests表中获取相应的姓名和电子邮件（如果有的话）。如果myguests表中没有匹配的 ID，则姓名和电子邮件将返回 NULL 值。

现在打开 index.php，将 $sql 查询替换为上述内容。运行后，结果将如下：

如果查看上面截图中的消息表，你会发现一些 ID 与任何用户 ID 都不匹配。这就是为什么该查询在姓名和电子邮件列中返回空值，因为它在左列中没有找到任何匹配项。

左连接

LEFT JOIN 连接两个表时，会返回左表中的所有值和右表中的匹配值，如果没有找到匹配值，还会在右表中返回 NULL。LEFT JOIN 的结构如下：

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

现在，让我们以“messages”表为右表，以“myguests”表为左表，从数据库中获取“Name”和“message”。

$sql = "SELECT CONCAT(myguests.firstname,' ',myguests.lastname) AS name, myguests.email, messages.message
From myguests
LEFT JOIN messages
ON messages.id = myguests.id";

现在打开 index.php，将 $sql 查询替换为上述内容。运行后，结果如下：

如果查看消息表，你会发现一些 ID 与任何用户 ID 都不匹配。这就是为什么此查询在 Message 列中返回 NULL，而在右列中找不到任何匹配的原因。

并集

MySQL 中的 UNION 将来自不同表的多个列合并为一个结果集。用于选择唯一值的 UNION 查询的结构是

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

以及从列中选择重复值：

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

现在，让我们从表中获取 ID。

$sql = "SELECT id FROM myguests
UNION
SELECT id FROM messages";

现在打开 index.php，将 $sql 查询替换为上面的查询。运行后，结果将是：

查询获取了两个表中的所有唯一 ID。

交叉连接或笛卡尔积

这种类型的联接会返回联接表中记录的笛卡尔积。它将返回一个由第一张表的每条记录和第二张表的每条记录组成的表。

交叉连接的语法是：

SELECT column-name-list
from table-name1
CROSS JOIN
table-name2;

现在，让我们用“myguests”表和“messages”表来展示 Cross JOIN：

假设我们想列出“myguests”表中姓名与“messages”表中信息的所有可能组合。我们可以使用交叉连接来实现这一目的：

SELECT myguests.firstname, myguests.lastname, messages.message
FROM myguests
CROSS JOIN
messages;

该查询将给出一个列表，其中包含“myguests”表中的姓名和“messages”表中的消息的所有组合。

自连接

自连接是一种将表中的行与表本身结合起来的连接类型。虽然我们可能无法立即看出它的用处，但它实际上有许多常见的应用。

自连接语法：

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

现在，让我们用“messages”表演示一下自连接：

假设我们要查找发件人相同的邮件对。我们可以使用自连接来实现这一目的：

SELECT m1.id, m1.sender, m2.id, m2.sender
FROM messages m1, messages m2
WHERE m1.sender = m2.sender
AND m1.id <> m2.id;

此查询将返回具有相同发件人的成对邮件，但不包括将邮件与自身进行比较的情况。

自连接在需要比较同一表中的行时特别有用，例如在分层结构或处理递归关系时。

全外连接

当左表（表 1）或右表（表 2）中的记录出现匹配时，全外连接会返回所有记录。它基本上结合了左连接和右连接的结果。

注意：全外连接可能会返回非常大的结果集！

全外连接语法：

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

下面有一个简单的例子来说明全外连接：

假设我们有两个表，“employees”和“departments”，我们要检索两个表中的所有记录，包括匹配记录和未匹配记录：

SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

此查询将返回所有团队成员记录（包括没有相应部门的记录）和所有部门记录（包括没有任何相关雇员的记录）。

自然连接

自然连接是 SQL 中的一种连接类型，它根据具有相同名称和数据类型的列来合并表中的记录。这种连接只在结果集中包含一次常见列。

要使用自然连接，可以使用以下语法将两个或多个表结合起来：

SELECT column_1, column_2, ..., column_n
FROM table_1
NATURAL JOIN table_2;

让我们用“messages”表来说明自然连接：

假设我们有两个表，“messages”表和“users”表，这两个表都包含一个名为“user_id”的列。我们的目标是从两个表中获取“user_id”列值匹配的所有记录：

SELECT *
FROM messages
NATURAL JOIN users;

该查询将对“user_id”列执行自然连接，合并两个表中“user_id”值匹配的记录。生成的数据集将只显示一次“user_id”列。

总结

现实世界中几乎所有的数据查询都要使用 SQL 中的某种连接。连接就像拼图一样，可以帮助我们从不同来源收集信息。

在本文中，我们讨论了 SQL 连接以及 MySQL 如何使用它们来组合来自不同表的数据。

通过了解连接，你就能在 PHP 应用程序中顺利处理数据，无论其结构如何。

常见问题

Q1. 使用什么 SQL 命令可以连接两个表？

答：可以使用 SQL 中的 INNER JOIN 命令，根据共同的列合并两个表。它只检索两个表中匹配的记录，确保以结构化和高效的方式合并相关数据。

例如：

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Q2. 如何在 MySQL 中连接两个数据库？

答：在 MySQL 中，可以使用 JOIN 语句连接两个不同数据库的表。语法格式如下：

SELECT
tb_1.*,
tb_2.*
FROM database_1.table_name_1 tb_1
JOIN database_2.table_name_2 tb_2 ON tb_1.id = tb_2.id;

该查询通过使用 database_name.table_name 引用不同数据库中的表来检索匹配记录。

Q3. 如何用 SQL 连接多对多关系的两个表？

答：要连接具有多对多关系的两个表，请使用包含两个表外键的连接表。

例如，如果有事件和参与者，可以使用 event_participants 这样的连接表将它们连接起来。然后，使用内联连接检索相关记录：

SELECT e.event_name, p.participant_name
FROM events e
JOIN event_participants ep ON e.id = ep.event_id
JOIN participants p ON ep.participant_id = p.id;

Q4. 可以连接没有任何关系的两个表吗？

答：可以。要连接两个没有任何关系的表，可以使用 CROSS JOIN 或 UNION。

• CROSS JOIN 将一个表中的每一条记录与另一个表中的每一条记录配对。
• UNION 将两个 SELECT 查询的结果合并为一个结果，只要这两个表具有相同的结构。

这两种方法都允许合并表，即使没有共享键或关系。

Q5. 我们可以连接两个列名相同的 MySQL 表吗？

答：可以，你可以连接列名相同的两个 MySQL 表。为避免混淆，请使用别名来区分列。这可确保正确连接表而不会出现任何问题。

例如：

SELECT t1.column_name AS t1_column, t2.column_name AS t2_column
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id;

Q6. 如何在 SQL 和 MySQL 中连接三个表？

答：要在 SQL 或 MySQL 中连接三个表，可以使用多重连接。只需连接前两个表，然后将第三个表连接到第一个连接的结果。这样做不一定需要子查询，因为可以在单个查询中直接连接所有表。

例如

SELECT t1.column1, t2.column2, t3.column3
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
JOIN table3 t3 ON t2.id = t3.id;

Q7. 如何在不使用连接的情况下用 SQL 连接两个表？

答：在 SQL 中可以不使用 JOIN 关键字连接两个表，方法是在 FROM 子句中列出两个表，中间用逗号隔开。这被称为隐式连接。

例如

SELECT *
FROM TableA a, TableB b
WHERE a.id = b.id;

虽然这种方法可行，但为了清晰和性能，一般还是使用显式 JOIN 语句更好。

Q8. 什么是 MySQL 中的连接？

答：MySQL 中的连接是根据相关列（通常是外键）将两个或多个表中的记录组合在一起。通过指定如何通过这些键连接表，可以在单个查询中检索多个表中的数据。

Q9. 连接与内部连接一样吗？

答：是的，在 MySQL 中，JOIN 与 INNER JOIN 相同。两者都返回被连接的两个表中具有匹配值的记录。如果找不到匹配，则不返回记录。

Q10. 如何在 MySQL 中连接两个表？

答：要在 MySQL 中连接两个表，请结合使用 JOIN 子句和 ON 关键字来指定匹配条件。例如

SELECT columns  
FROM table1  
JOIN table2  
ON table1.common_column = table2.common_column;

Q11. 如何在 MySQL 中合并两个表？

答：要在 MySQL 中合并两个表，请使用 UNION 操作符。这会将两个表中的行合并为一个结果。确保两个表有相同的列数和兼容的数据类型。

举例说明：

SELECT * FROM table1
UNION
SELECT * FROM table2;

注意：如果要包含所有重复行，请使用 UNION ALL 而不是 UNION。