Mysq中JOIN有什么用

50次阅读

共计 6790 个字符，预计需要花费 17 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章给大家分享的是有关 Mysq 中 JOIN 有什么用的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考。一起跟随丸趣 TV 小编过来看看吧。

0 索引 JOIN 语句的执行顺序 INNER/LEFT/RIGHT/FULL JOIN 的区别 ON 和 WHERE 的区别 1 概述

一个完整的 SQL 语句中会被拆分成多个子句，子句的执行过程中会产生虚拟表 (vt)，但是结果只返回最后一张虚拟表。从这个思路出发，我们试着理解一下 JOIN 查询的执行过程并解答一些常见的问题。
如果之前对不同 JOIN 的执行结果没有概念，可以结合这篇文章往下看

2 JOIN 的执行顺序

以下是 JOIN 查询的通用结构

SELECT row_list 
 FROM left_table 
 inner|left|right JOIN right_table 
 ON join condition 
 WHERE where_condition

它的执行顺序如下(SQL 语句里第一个被执行的总是 FROM 子句)：

FROM: 对左右两张表执行笛卡尔积，产生第一张表 vt1。行数为 n *m（n 为左表的行数，m 为右表的行数 ON: 根据 ON 的条件逐行筛选 vt1，将结果插入 vt2 中 JOIN: 添加外部行，如果指定了 LEFT JOIN(LEFT OUTER JOIN)，则先遍历一遍左表的每一行，其中不在 vt2 的行会被插入到 vt2，该行的剩余字段将被填充为 NULL，形成 vt3；如果指定了 RIGHT JOIN 也是同理。但如果指定的是 INNER JOIN，则不会添加外部行，上述插入过程被忽略，vt2=vt3（所以 INNER JOIN 的过滤条件放在 ON 或 WHERE 里执行结果是没有区别的，下文会细说）WHERE: 对 vt3 进行条件过滤，满足条件的行被输出到 vt4SELECT: 取出 vt4 的指定字段到 vt5

下面用一个例子介绍一下上述联表的过程（这个例子不是个好的实践，只是为了说明 join 语法）

3 举例

创建一个用户信息表：

CREATE TABLE `user_info` (`userid` int(11) NOT NULL,
 `name` varchar(255) NOT NULL,
 UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

再创建一个用户余额表：

CREATE TABLE `user_account` (`userid` int(11) NOT NULL,
 `money` bigint(20) NOT NULL,
 UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

随便导入一些数据：

select * from user_info;
+--------+------+
| userid | name |
+--------+------+
| 1001 | x |
| 1002 | y |
| 1003 | z |
| 1004 | a |
| 1005 | b |
| 1006 | c |
| 1007 | d |
| 1008 | e |
+--------+------+
8 rows in set (0.00 sec)
select * from user_account;
+--------+-------+
| userid | money |
+--------+-------+
| 1001 | 22 |
| 1002 | 30 |
| 1003 | 8 |
| 1009 | 11 |
+--------+-------+
4 rows in set (0.00 sec)

一共 8 个用户有用户名，4 个用户的账户有余额。
取出 userid 为 1003 的用户姓名和余额，SQL 如下：

SELECT i.name, a.money 
 FROM user_info as i 
 LEFT JOIN user_account as a 
 ON i.userid = a.userid 
 WHERE a.userid = 1003;

第一步：执行 FROM 子句对两张表进行笛卡尔积操作

笛卡尔积操作后会返回两张表中所有行的组合，左表 user_info 有 8 行，右表 user_account 有 4 行，生成的虚拟表 vt1 就是 8 *4=32 行：

SELECT * FROM user_info as i LEFT JOIN user_account as a ON 1;
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1001 | 22 |
| 1003 | z | 1001 | 22 |
| 1004 | a | 1001 | 22 |
| 1005 | b | 1001 | 22 |
| 1006 | c | 1001 | 22 |
| 1007 | d | 1001 | 22 |
| 1008 | e | 1001 | 22 |
| 1001 | x | 1002 | 30 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1002 | 30 |
| 1004 | a | 1002 | 30 |
| 1005 | b | 1002 | 30 |
| 1006 | c | 1002 | 30 |
| 1007 | d | 1002 | 30 |
| 1008 | e | 1002 | 30 |
| 1001 | x | 1003 | 8 |
| 1002 | y | 1003 | 8 |
| 1003 | z | 1003 | 8 |
| 1004 | a | 1003 | 8 |
| 1005 | b | 1003 | 8 |
| 1006 | c | 1003 | 8 |
| 1007 | d | 1003 | 8 |
| 1008 | e | 1003 | 8 |
| 1001 | x | 1009 | 11 |
| 1002 | y | 1009 | 11 |
| 1003 | z | 1009 | 11 |
| 1004 | a | 1009 | 11 |
| 1005 | b | 1009 | 11 |
| 1006 | c | 1009 | 11 |
| 1007 | d | 1009 | 11 |
| 1008 | e | 1009 | 11 |
+--------+------+--------+-------+
32 rows in set (0.00 sec)

第二步：执行 ON 子句过滤掉不满足条件的行

ON i.userid = a.userid 过滤之后 vt2 如下：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+

第三步：JOIN 添加外部行

LEFT JOIN 会将左表未出现在 vt2 的行插入进 vt2，每一行的剩余字段将被填充为 NULL，RIGHT JOIN 同理
本例中用的是 LEFT JOIN，所以会将左表 user_info 剩下的行都添上生成表 vt3：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
| 1004 | a | NULL | NULL |
| 1005 | b | NULL | NULL |
| 1006 | c | NULL | NULL |
| 1007 | d | NULL | NULL |
| 1008 | e | NULL | NULL |
+--------+------+--------+-------+

第四步：WHERE 条件过滤

WHERE a.userid = 1003 生成表 vt4：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+

第五步：SELECT

SELECT i.name, a.money 生成 vt5：

+------+-------+
| name | money |
+------+-------+
| z | 8 |
+------+-------+

虚拟表 vt5 作为最终结果返回给客户端

介绍完联表的过程之后，我们看看常用 JOIN 的区别

4 INNER/LEFT/RIGHT/FULL JOIN 的区别 INNER JOIN…ON…: 返回左右表互相匹配的所有行（因为只执行上文的第二步 ON 过滤，不执行第三步添加外部行）LEFT JOIN…ON…: 返回左表的所有行，若某些行在右表里没有相对应的匹配行，则将右表的列在新表中置为 NULLRIGHT JOIN…ON…: 返回右表的所有行，若某些行在左表里没有相对应的匹配行，则将左表的列在新表中置为 NULLINNER JOIN

拿上文的第三步添加外部行来举例，若 LEFT JOIN 替换成 INNER JOIN，则会跳过这一步，生成的表 vt3 与 vt2 一模一样：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+

RIGHT JOIN

若 LEFT JOIN 替换成 RIGHT JOIN，则生成的表 vt3 如下：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
| NULL | NULL | 1009 | 11 |
+--------+------+--------+-------+

因为 user_account（右表）里存在 userid=1009 这一行，而 user_info（左表）里却找不到这一行的记录，所以会在第三步插入以下一行：

| NULL | NULL | 1009 | 11 |

FULL JOIN

上文引用的文章中提到了标准 SQL 定义的 FULL JOIN，这在 mysql 里是不支持的，不过我们可以通过 LEFT JOIN + UNION + RIGHT JOIN 来实现 FULL JOIN：

SELECT * 
 FROM user_info as i 
 RIGHT JOIN user_account as a 
 ON a.userid=i.userid
union 
SELECT * 
 FROM user_info as i 
 LEFT JOIN user_account as a 
 ON a.userid=i.userid;

他会返回如下结果：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
| NULL | NULL | 1009 | 11 |
| 1004 | a | NULL | NULL |
| 1005 | b | NULL | NULL |
| 1006 | c | NULL | NULL |
| 1007 | d | NULL | NULL |
| 1008 | e | NULL | NULL |
+--------+------+--------+-------+

ps：其实我们从语义上就能看出 LEFT JOIN 和 RIGHT JOIN 没什么差别，两者的结果差异取决于左右表的放置顺序，以下内容摘自 mysql 官方文档：

RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.

所以当你纠结使用 LEFT JOIN 还是 RIGHT JOIN 时，尽可能只使用 LEFT JOIN 吧

5 ON 和 WHERE 的区别

上文把 JOIN 的执行顺序了解清楚之后，ON 和 WHERE 的区别也就很好理解了。
举例说明:

SELECT * 
 FROM user_info as i
 LEFT JOIN user_account as a
 ON i.userid = a.userid and i.userid = 1003;

SELECT * 
 FROM user_info as i
 LEFT JOIN user_account as a
 ON i.userid = a.userid where i.userid = 1003;

第一种情况 LEFT JOIN 在执行完第二步 ON 子句后，筛选出满足 i.userid = a.userid and i.userid = 1003 的行，生成表 vt2，然后执行第三步 JOIN 子句，将外部行添加进虚拟表生成 vt3 即最终结果：

vt2:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | NULL | NULL |
| 1002 | y | NULL | NULL |
| 1003 | z | 1003 | 8 |
| 1004 | a | NULL | NULL |
| 1005 | b | NULL | NULL |
| 1006 | c | NULL | NULL |
| 1007 | d | NULL | NULL |
| 1008 | e | NULL | NULL |
+--------+------+--------+-------+

而第二种情况 LEFT JOIN 在执行完第二步 ON 子句后，筛选出满足 i.userid = a.userid 的行，生成表 vt2；再执行第三步 JOIN 子句添加外部行生成表 vt3；然后执行第四步 WHERE 子句，再对 vt3 表进行过滤生成 vt4，得的最终结果：

vt2:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
| 1004 | a | NULL | NULL |
| 1005 | b | NULL | NULL |
| 1006 | c | NULL | NULL |
| 1007 | d | NULL | NULL |
| 1008 | e | NULL | NULL |
+--------+------+--------+-------+
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+

如果将上例的 LEFT JOIN 替换成 INNER JOIN，不论将条件过滤放到 ON 还是 WHERE 里，结果都是一样的，因为 INNER JOIN 不会执行第三步添加外部行

SELECT * 
 FROM user_info as i
 INNER JOIN user_account as a
 ON i.userid = a.userid and i.userid = 1003;

SELECT * 
 FROM user_info as i
 INNER JOIN user_account as a
 ON i.userid = a.userid where i.userid = 1003;

返回结果都是:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+

感谢各位的阅读！关于 Mysq 中 JOIN 有什么用就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到吧！

向 AI 问一下细节

丸趣 TV 网 – 提供最优质的资源集合！

正文完