0索引JOIN语句的执行顺序INNER/LEFT/RIGHT/FULLJOIN的区别ON和WHERE的区别1概述一条完整的SQL语句会被拆分成多个子句。语句执行过程中会生成一个虚拟表（vt），但结果只会返回最后一个虚拟表。从这个思路出发，我们尝试理解JOIN查询的执行过程，并回答一些常见问题。如果之前对不同JOIN的执行结果没有概念，可以结合2JOIN的执行顺序阅读本文下面是JOIN查询的一般结构SELECTFROMJOINONWHERE其执行顺序如下（SQL语句中最先执行的永远是FROM子句）：FROM：对左右表进行笛卡尔积生成第一个表vt1。行数为n*m（n为左表行数，m为右表行数）ON：根据ON的条件逐行过滤vt1，将结果插入vt2JOIN：添加外部行，如果是LEFTJOIN(LEFTOUTERJOIN)，则先遍历左表的每一行，将不在vt2中的行插入到vt2中，并用该行剩余的字段填充NULL形成vt3；如果指定RIGHTJOIN，也是如此。但是如果指定了INNERJOIN，则不会添加外部行，忽略上面的插入过程，vt2=vt3（所以INNERJOIN的过滤条件放在ON或者WHERE，执行结果没有区别，下面会详细介绍）WHERE:对vt3进行条件过滤，将满足条件的行输出到vt4SELECT:取出vt4的指定字段，发送给vt5。下面用一个例子来介绍一下上面的连接表的过程（这个例子不是一个好的做法，只是为了说明连接语法）3创建用户信息表的例子：CREATETABLE`user_info`(`userid`int(11)NOTNULL,`name`varchar(255)NOTNULL,UNIQUE`userid`(`userid`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4创建另一个用户余额表：CREATETABLE`user_account`(`userid`int(11)NOTNULL,`money`bigint(20)NOTNULL,UNIQUE`userid`(`userid`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4随机导入一些数据：select*fromuser_info;+--------+-----+|用户ID|名称|+------+------+|1001|x||1002|y||1003|z||1004|a||1005|b||1006|c||1007|d||1008|e|+------+------+8rowsinset（0.00秒）select*fromuser_account;+--------+--------+|userid|money|+--------+--------+|1001|22||1002|30||1003|8||1009|11|+--------+--------+4rowsinset(0.00sec)共有8个用户有用户名，4个用户的账户有余额。取出userid为1003的用户名和balance，SQL如下：SELECTi.name,a.moneyFROMuser_infoasiLEFTJOINuser_accountasaONi.userid=a.useridWHEREa.userid=1003;第一步：执行FROM子句对两张表进行笛卡尔乘积运算Cal乘积运算后，会返回两张表中所有行的组合。左表user_info有8行，右表user_account有4行。生成的虚拟表vt1有8*4=32行：SELECT*FROMuser_infoasiLEFTJOINuser_accountasaON1;+--------+-----+--------+--------+|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|+--------+------+--------+------+32rowsinset(0.00sec)第二步:执行ON子句，过滤掉不满足ONi.userid=a条件的行.userid过滤后，vt2如下：+--------+------+--------+--------+|userid|name|userid|钱|+--------+-----+--------+--------+|1001|x|1001|22||1002|y|1002|30||1003|z|1003|8|+--------+------+--------+--------+第三步：JOIN添加externalrowLEFTJOIN会将左表中没有出现在vt2中的行插入到vt2中，每行的剩余字段将填充为NULL。同理，本例中使用了LEFTJOIN来进行RIGHTJOIN，所以左表user_info将在生成表vt3中加入如下行：+--------+------+-------+--------+|用户标识|名称|用户标识|钱|+--------+------+--------+------+|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条件过滤WHEREa.userid=1003生成表vt4:+--------+------+--------+--------+|userid|name|userid|钱|+--------+------+--------+------+|1003|z|1003|8|+--------+------+--------+--------+第五步：SELECTSELECTi.name,a.moneyGeneratevt5:+------+------+|名字|金钱|+------+--------+|z|8|+------+-------+虚表vt5作为最终结果返回给客户端。介绍完连接表的过程，我们再来看看常用JOIN的区别4.INNER/LEFT/RIGHT/FULLJOININNERJOIN...ON..的区别.:返回左右表中所有相互匹配的行（因为上面只进行了第二步ON过滤，没有进行第三步添加外部行）LEFTJOIN...ON...：返回左表中的所有行，如果某些行在右表中没有对应的匹配行，则在新表中将右表的列设置为NULLRIGHTJOIN...ON...：返回全部右表的行，如果左表中有一些行如果表中没有对应的匹配行，则在新表中将左表的列设置为NULLINNERJOIN。以上面第三步添加外部行为例。如果将LEFTJOIN换成INNERJOIN，则跳过这一步，生成的表vt3和vt2完全一样：+--------+------+--------+--------+|用户标识|名称|用户标识|钱|+--------+------+--------+------+|1001|x|1001|22||1002|y|1002|30||1003|z|1003|8|+--------+------+--------+--------+RIGHTJOIN如果如果将LEFTJOIN替换为RIGHTJOIN，则生成的表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（左表）中没有这一行的记录，所以会在第三行插入下面一行step:|NULL|NULL|1009|11|FULLJOIN上面引用的文章提到了标准SQL定义的FULLJOIN，在mysql中是不支持的，但是我们可以通过LEFTJOIN+UNION+RIGHTJOIN来实现FULLJOIN:SELECT*FROMuser_infoasiRIGHTJOINuser_accountasaONa.userid=i.useridunionSELECT*FROMuser_infoasiLEFTJOINuser_accountasaONa.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：其实从语义上我们可以看出LEFTJOIN和RIGHTJOIN没有区别。两者结果的差异取决于左右表的放置顺序。以下内容摘自mysql官方文档：RIGHTJOIN的作用类似于LEFTJOIN。.所以当你纠结于使用LEFTJOIN还是RIGHTJOIN时，尽量只使用LEFTJOIN。5ON和WHERE的区别理解了上面JOIN的执行顺序之后，ON和WHERE的区别就很容易理解了例如：SELECT*FROMuser_infoasiLEFTJOINuser_accountasaONi.userid=a.useridandi.userid=1003;SELECT*FROMuser_infoasiLEFTJOINuser_accountasaONi.userid=a.useridwherei.userid=1003;第一种情况，执行第二步ON子句后过滤LEFTJOIN输出满足i.userid=a.userid和i.userid=1003的行，生成表vt2，然后执行第三步JOIN子句，将外部行添加到虚表中生成vt3，即最终结果：vt2:+--------+------+--------+-------+|userid|name|userid|money|+--------+------+--------+------+|1003|z|1003|8|+------+------+-------+--------+vt3:+---------+------+------+-------+|用户ID|名称|用户ID|金钱|+-------+------+----------+--------+|1001|x|空|空||1002|y|空|空||1003|z|1003|8||1004|a|空|空||1005|b|空|空||1006|c|空|空||1007|d|NULL|NULL||1008|e|NULL|NULL|+------+------+--------+--------+第二种情况，执行完第二个ON子句后，LEFTJOIN过滤掉满足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|+------+------+--------+-------+如果你更换上面例子中的LEFTJOIN和INNERJOIN，无论你把条件过滤放在ON还是WHERE，结果都是一样的，因为INNERJOIN不会执行第三步添加外部行SELECT*FROMuser_infoasiINNERJOINuser_accountasaONi.userid=a.useridandi.userid=1003;SELECT*FROMuser_infoasiINNERJOINuser_accountasaONi.userid=a.useridwherei.userid=1003;返回结果为：+------+------+---------+------+|userid|name|userid|money|+------+-----+------+-------+|1003|z|1003|8|+------+-----+---------+------+参考资料《MySQL技术内幕：SQL编程》SQLJoins-W3Schoolssql-什么是“INNERJOIN&rdq”的区别哦；和“外部连接”？MySQL::MySQL8.0参考手册::13.2.10.2JOIN语法SQLJoin的可视化表示Join(SQL)-维基百科)
