Skip to main content

MySQL Join为初学者提供了便利

MySQL连接子句简介

关系数据库由多个相关表组成,这些表使用公共列(即外键列)链接在一起。因此,从业务角度来看,每个表中的数据都是不完整的。

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

MySQL Transaction: orders & orderDetails Tables

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

这就是为什么会有人加入。

联接是一种基于表之间公共列的值在一个(自联接)或多个表之间链接数据的方法。

MySQL支持以下类型的连接:

要连接表,可以使用交叉连接、内部连接、左连接或右连接子句。join子句用于FROM子句之后出现的SELECT语句。

请注意,MySQL还不支持完整的外部连接。

建立样本表

首先,创建两个名为“成员和委员会”的表:

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)
);

第二,在成员和委员会的表格中插入一些行:

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

INSERT INTO committees(name)
VALUES('John'),('Mary'),('Amelia'),('Joe');

第三,从成员和委员会的表格中查询数据:

SELECT * FROM members;
+-----------+--------+
| member_id | name |
+-----------+--------+
| 1 | John |
| 2 | Jane |
| 3 | Mary |
| 4 | David |
| 5 | Amelia |
+-----------+--------+
5 rows in set (0.00 sec)
SELECT * FROM committees;
+--------------+--------+
| committee_id | name |
+--------------+--------+
| 1 | John |
| 2 | Mary |
| 3 | Amelia |
| 4 | Joe |
+--------------+--------+
4 rows in set (0.00 sec)

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

MySQL内部连接子句

下面显示了连接两个表(表1和表2)的内部join子句的基本语法:

SELECT column_list
FROM table_1
INNER JOIN table_2 ON join_condition;

internal join子句根据一个称为连接谓词的条件连接两个表。

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

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

如果联接条件使用相等运算符(=),且两个表中用于匹配的列名相同,则可以改用USING子句:

SELECT column_list
FROM table_1
INNER JOIN table_2 USING (column_name);

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

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;
+-----------+--------+--------------+-----------+
| 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)

在本例中,inner join子句使用表members和committees中的name列中的值进行匹配。

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

mysql join - inner 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);

MySQL左连接子句

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

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

如果两行中的值满足join条件,left join子句将创建一个新行,其列包含两个表中所有行的列,并将该行包含在结果集中。

如果两行中的值不匹配,left join子句仍会创建一个新行,其列包含左表中该行的列,而右表中该行的列则为NULL。

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

如果找不到右表中的匹配行,则左联接会对结果集中右表中的行的列使用null。

下面是连接两个表的left join子句的基本语法:

SELECT column_list 
FROM table_1
LEFT JOIN table_2 ON join_condition;

如果两个表中用于匹配的列相同,左联接也支持USING子句:

SELECT column_list 
FROM table_1
LEFT JOIN table_2 USING (column_name);

以下示例使用left join子句将成员与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);
+-----------+--------+--------------+-----------+
| 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)

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

mysql join - left join

此语句使用left join子句和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);

要查找非委员会成员的成员,请添加WHERE子句,并按如下所示使用ISNULL运算符:

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;
+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
| 2 | Jane | NULL | NULL |
| 4 | David | NULL | NULL |
+-----------+--------+--------------+-----------+
2 rows in set (0.00 sec)

通常,这种查询模式可以在左表中找到右表中没有相应行的行。

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

mysql join - left join - only rows in the left table

MySQL右连接子句

right join子句与left join子句类似,只是对左表和右表的处理是相反的。右联接开始从右表而不是左表中选择数据。

right join子句选择右表中的所有行,并匹配左表中的行。如果右表中的一行没有左表中匹配的行,则左表的列在最终结果集中将为NULL。

下面是右连接的语法:

SELECT column_list 
FROM table_1
RIGHT JOIN table_2 ON join_condition;

与left join子句类似,right子句也支持USING语法:

SELECT column_list 
FROM table_1
RIGHT JOIN table_2 USING (column_name);

要在右表中查找左表中没有相应行的行,还可以使用WHERE子句和IS NULL运算符:

SELECT column_list 
FROM table_1
RIGHT JOIN table_2 USING (column_name)
WHERE column_table_1 IS NULL;

此语句使用右联接来联接成员和委员会表:

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;
+-----------+--------+--------------+-----------+
| 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)

此维恩图说明了正确的连接:

mysql join - right join

以下语句使用带有USING语法的right join子句:

SELECT 
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
RIGHT JOIN committees c USING(name);

要查找不在“成员”表中的委员会成员,请使用以下查询:

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;
+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
| NULL | NULL | 4 | Joe |
+-----------+--------+--------------+-----------+
1 row in set (0.00 sec)

此维恩图说明了如何使用正确的联接来选择仅存在于正确表中的数据:

MySQL交叉连接子句

与内部联接、左联接和右联接不同,交叉联接子句没有联接条件。

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

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

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

SELECT select_list
FROM table_1
CROSS JOIN table_2;

本例使用交叉连接子句将成员与committees表连接起来:

SELECT 
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
CROSS JOIN committees c;
+-----------+--------+--------------+-----------+
| 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)

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

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