Home » MySQL Basics » MySQL Join

MySQL Join

摘要：在本教程中，您将学习SELECT语句中的各种 MySQL join 子句来查询两个表中的数据。

MySQL join 子句简介

关系数据库由多个使用公共列（称为外键列）链接在一起的相关表组成。因此，从业务角度来看，每个表中的数据都是不完整的。

例如，在示例数据库中，我们有使用orderNumber列链接的orders和orderdetails表：

MySQL 事务：订单和 orderDetails 表

要获取完整的订单信息，您需要从orders和orderdetails表中查询数据。

这就是连接出现的原因。

连接是一种根据表之间公共列的值在一个（自连接）或多个表之间链接数据的方法。

MySQL 支持以下类型的连接：

  1. 内部联接
  2. 左连接
  3. 右连接
  4. 交叉连接

要连接表，可以使用交叉连接、内连接、左连接或右连接子句。 join 子句用在SELECT语句中，出现在FROM子句之后。

请注意，MySQL 尚不支持FULL OUTER JOIN 。

设置样本表

首先，创建两个表，分别称为members和committees ：

CREATE TABLE members (
    member_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (member_id)
);

CREATE TABLE committees (
    committee_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (committee_id)
);Code language: SQL (Structured Query Language) (sql)

其次，在表members和committees中插入一些行：

INSERT INTO members(name)
VALUES('John'),('Jane'),('Mary'),('David'),('Amelia');

INSERT INTO committees(name)
VALUES('John'),('Mary'),('Amelia'),('Joe');Code language: SQL (Structured Query Language) (sql)

第三，从表members和committees中查询数据：

SELECT * FROM members;Code language: SQL (Structured Query Language) (sql)
+-----------+--------+
| member_id | name   |
+-----------+--------+
|         1 | John   |
|         2 | Jane   |
|         3 | Mary   |
|         4 | David  |
|         5 | Amelia |
+-----------+--------+
5 rows in set (0.00 sec)Code language: plaintext (plaintext)
SELECT * FROM committees;Code language: SQL (Structured Query Language) (sql)
+--------------+--------+
| committee_id | name   |
+--------------+--------+
|            1 | John   |
|            2 | Mary   |
|            3 | Amelia |
|            4 | Joe    |
+--------------+--------+
4 rows in set (0.00 sec)Code language: plaintext (plaintext)

有些成员是委员会成员，有些则不是。另一方面，有些委员会成员在members表中，有些则不在。

MySQL INNER JOIN 子句

下面显示了连接两个表table_1和table_2的内连接子句的基本语法：

SELECT column_list
FROM table_1
INNER JOIN table_2 ON join_condition;Code language: SQL (Structured Query Language) (sql)

内部联接子句根据称为联接谓词的条件联接两个表。

内连接子句将第一个表中的每一行与第二个表中的每一行进行比较。

如果两行中的值都满足联接条件，则内部联接子句将创建一个新行，该行的列包含两个表中两行的所有列，并将该新行包含在结果集中。换句话说，内部联接子句仅包含两个表中的匹配行。

如果连接条件使用等于运算符（ = ）并且两个表中用于匹配的列名相同，则可以使用USING子句代替：

SELECT column_list
FROM table_1
INNER JOIN table_2 USING (column_name);Code language: SQL (Structured Query Language) (sql)

以下语句使用内部联接子句来查找同时也是委员会成员的成员：

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
INNER JOIN committees c ON c.name = m.name;Code language: SQL (Structured Query Language) (sql)
+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
|         1 | John   |            1 | John      |
|         3 | Mary   |            2 | Mary      |
|         5 | Amelia |            3 | Amelia    |
+-----------+--------+--------------+-----------+
3 rows in set (0.00 sec)Code language: plaintext (plaintext)

在此示例中，内部联接子句使用表members和committees中name列中的值进行匹配。

下面的维恩图说明了内连接：

mysql join - 内连接

由于两个表使用同一列进行匹配，因此您可以使用USING子句，如以下查询所示：

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
INNER JOIN committees c USING(name);Code language: SQL (Structured Query Language) (sql)

MySQL LEFT JOIN 子句

与内连接类似，左连接也需要连接谓词。使用左连接连接两个表时，会引入左表和右表的概念。

左连接选择从左表开始的数据。对于左表中的每一行，左连接将与右表中的每一行进行比较。

如果两行中的值满足联接条件，则左联接子句将创建一个新行，该行的列包含两个表中行的所有列，并将该行包含在结果集中。

如果两行中的值不匹配，左连接子句仍创建一个新行，该新行的列包含左表中该行的列，而右表中该行的列为NULL 。

换句话说，无论右表中是否存在匹配的行，左连接都会选择左表中的所有数据。

如果右表中没有找到匹配的行，则左连接对结果集中右表中的行的列使用 NULL。

以下是连接两个表的左连接子句的基本语法：

SELECT column_list 
FROM table_1 
LEFT JOIN table_2 ON join_condition;Code language: SQL (Structured Query Language) (sql)

如果两个表中用于匹配的列相同，则左连接还支持USING子句：

SELECT column_list 
FROM table_1 
LEFT JOIN table_2 USING (column_name);Code language: SQL (Structured Query Language) (sql)

以下示例使用左连接子句将members与committees表连接起来：

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
LEFT JOIN committees c USING(name);Code language: SQL (Structured Query Language) (sql)
+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
|         1 | John   |            1 | John      |
|         2 | Jane   |         NULL | NULL      |
|         3 | Mary   |            2 | Mary      |
|         4 | David  |         NULL | NULL      |
|         5 | Amelia |            3 | Amelia    |
+-----------+--------+--------------+-----------+
5 rows in set (0.00 sec)Code language: plaintext (plaintext)

下面的维恩图说明了左连接：

mysql 连接-左连接

此语句使用带有USING语法的左连接子句：

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
LEFT JOIN committees c USING(name);Code language: SQL (Structured Query Language) (sql)

要查找非委员会成员的成员，请添加WHERE子句和IS NULL运算符，如下所示：

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
LEFT JOIN committees c USING(name)
WHERE c.committee_id IS NULL;Code language: SQL (Structured Query Language) (sql)
+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
|         2 | Jane   |         NULL | NULL      |
|         4 | David  |         NULL | NULL      |
+-----------+--------+--------------+-----------+
2 rows in set (0.00 sec)Code language: plaintext (plaintext)

通常，此查询模式可以找到左表中在右表中没有对应行的行。

此维恩图说明了如何使用左联接来选择仅存在于左表中的行：

mysql join - 左连接 - 仅左表中的行

MySQL RIGHT JOIN 子句

右连接子句与左连接子句类似，只是左右表的处理相反。右连接开始从右表而不是左表中选择数据。

右连接子句选择右表中的所有行并匹配左表中的行。如果右表中的行与左表中没有匹配的行，则左表的列在最终结果集中将为NULL 。

这是右连接的语法：

SELECT column_list 
FROM table_1 
RIGHT JOIN table_2 ON join_condition;Code language: SQL (Structured Query Language) (sql)

与左连接子句类似，右连接子句也支持USING语法：

SELECT column_list 
FROM table_1 
RIGHT JOIN table_2 USING (column_name);Code language: SQL (Structured Query Language) (sql)

要查找右表中与左表中没有对应行的行，还可以使用带有IS NULL运算符的WHERE子句：

SELECT column_list 
FROM table_1 
RIGHT JOIN table_2 USING (column_name)
WHERE column_table_1 IS NULL;Code language: SQL (Structured Query Language) (sql)

此语句使用右连接来连接members和committees表：

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
RIGHT JOIN committees c on c.name = m.name;Code language: SQL (Structured Query Language) (sql)
+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
|         1 | John   |            1 | John      |
|         3 | Mary   |            2 | Mary      |
|         5 | Amelia |            3 | Amelia    |
|      NULL | NULL   |            4 | Joe       |
+-----------+--------+--------------+-----------+
4 rows in set (0.00 sec)Code language: plaintext (plaintext)

此维恩图说明了右连接：

mysql 连接-右连接

以下语句使用带有USING语法的右连接子句：

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
RIGHT JOIN committees c USING(name);Code language: SQL (Structured Query Language) (sql)

要查找不在members表中的委员会成员，请使用以下查询：

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
RIGHT JOIN committees c USING(name)
WHERE m.member_id IS NULL;Code language: SQL (Structured Query Language) (sql)
+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
|      NULL | NULL   |            4 | Joe       |
+-----------+--------+--------------+-----------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

此维恩图说明了如何使用右连接来选择仅存在于右表中的数据：

MySQL CROSS JOIN 子句

与内连接、左连接和右连接不同，交叉连接子句没有连接条件。

交叉联接对联接表中的行进行笛卡尔积。交叉联接将第一个表中的每一行与右表中的每一行组合起来以形成结果集。

假设第一个表有n行，第二个表有m行。连接表的交叉连接将返回nxm行。

下面显示了交叉连接子句的语法：

SELECT select_list
FROM table_1
CROSS JOIN table_2;Code language: SQL (Structured Query Language) (sql)

此示例使用交叉联接子句将members与committees表连接起来：

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
CROSS JOIN committees c;Code language: SQL (Structured Query Language) (sql)
+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
|         1 | John   |            4 | Joe       |
|         1 | John   |            3 | Amelia    |
|         1 | John   |            2 | Mary      |
|         1 | John   |            1 | John      |
|         2 | Jane   |            4 | Joe       |
|         2 | Jane   |            3 | Amelia    |
|         2 | Jane   |            2 | Mary      |
|         2 | Jane   |            1 | John      |
|         3 | Mary   |            4 | Joe       |
|         3 | Mary   |            3 | Amelia    |
|         3 | Mary   |            2 | Mary      |
|         3 | Mary   |            1 | John      |
|         4 | David  |            4 | Joe       |
|         4 | David  |            3 | Amelia    |
|         4 | David  |            2 | Mary      |
|         4 | David  |            1 | John      |
|         5 | Amelia |            4 | Joe       |
|         5 | Amelia |            3 | Amelia    |
|         5 | Amelia |            2 | Mary      |
|         5 | Amelia |            1 | John      |
+-----------+--------+--------------+-----------+
20 rows in set (0.00 sec)Code language: plaintext (plaintext)

交叉连接对于生成计划数据非常有用。例如，您可以通过使用客户、产品和年份的交叉联接来执行销售计划。

在本教程中，您学习了各种 MySQL 连接语句，包括交叉连接、内连接、左连接和右连接，以查询两个表中的数据。

本教程有帮助吗？
MySQL Alias
Previously
MySQL Alias
Up Next
MySQL INNER JOIN
MySQL INNER JOIN