SQL基础篇（一）---JOIN语句执行流程&ON与WHERE的区别

本文主要记录了 MySQL 中的 JOIN 语句具体执行流程,同时分析了 ON 与 WHERE 条件的区别。

一个完整的 SQL 语句中会被拆分成多个子句，子句的执行过程中会产生虚拟表（VT），经过各种条件后生成的最后一张虚拟表就是返回的结果。

以下是 JOIN 查询的通用结构：

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

SQL 语句里第一个被执行的总是 FROM 子句

上文代码的执行顺序如下：

  • FROM：执行 FROM 子句对两张表进行笛卡尔积操作，
    • 对左右两张表执行笛卡尔积，产生第一张表 vt1。行数为 n*m（ n 为左表的行数，m 为右表的行数)
  • ON: 执行 ON 子句过滤掉不满足条件的行
    • 根据 ON 的条件逐行筛选 vt1，将结果插入 vt2 中
  • JOIN:添加外部行
    • 如果是 LEFT JOIN，则先遍历一遍左表的每一行，其中不在 vt2 的行会被添加到 vt2，该行的剩余字段将被填充为NULL，形成 vt3；RIGHT JOIN同理。但如果指定的是 INNER JOIN，则不会添加外部行，上述插入过程被忽略，vt3 就是 vt2。
  • WHERE: 条件过滤
    • 根据 WHERE 条件对 vt3 进行条件过滤产生 vt4
  • SELECT: 查询指定字段
    • 取出 vt4 的指定字段形成 vt5

创建一个用户信息表：

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

 

再创建一个用户余额表：

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

任意导入一些数据：

1 insert into user_info values(1001,'x'),(1002,'y'),(1003,'z'),(1004,'a'),(1005,'b'),(1006,'c'),(1007,'d'),(1008,'e');
3 insert into user_account values(1001,22),(1002,30),(1003,8),(1009,11);

一共 8 个用户有用户名，4 个用户的账户有余额。

 

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

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

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

 

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

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

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

 

2.3 第三步：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 |
+--------+------+--------+-------+
复制代码

 

2.4 第四步：WHERE条件过滤

WHERE a.userid = 1003 生成表 vt4：

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

2.5 第五步：SELECT

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

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

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

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

 

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

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

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

若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 |
+--------+------+--------+-------+

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

|   NULL | NULL |   1009 |    11 |
 
 

FULL JOIN 相当于把 LEFT JOIN 和 RIGHT JOIN 都执行一次，会返回如下结果：

复制代码
+--------+------+--------+-------+
| 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吧。

 

上文把 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 |
+--------+------+--------+-------+
vt3:
+--------+------+--------+-------+
| 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 |
+--------+------+--------+-------+
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 |
+--------+------+--------+-------+
vt4:
+--------+------+--------+-------+
| 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 |
+--------+------+--------+-------+
 

只要记住联表语句执行过程之后，应对各种 JOIN 语句应该都比较轻松了。

 

https://dev.mysql.com/doc/refman/8.0/en/join.html

https://mp.weixin.qq.com/s/LfbSmwwDy5QkxXsicjMjLA

https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

 

posted @   宽扁担  阅读(1303)  评论(0)  编辑  收藏  举报
相关博文：
· SQL小技巧（二）---CREATE TABLE AS 的执行问题
· 深度神经网络（DNN，Deep Neural Network）
· MySQL笔记(一)SQL基础
· SQL中ON和WHERE的区别
· sql_Join执行原理
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！
点击右上角即可分享
微信分享提示