0索引JOIN语句执行顺序INNER/LEFT/RIGHT/FULLJOINON和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;+--------+------+|用户名|名字|+------+-----+|1001|x||1002|是||1003|z||1004|一个||1005|b||1006|c||1007|d||1008|-----集合中+8行（0.00秒）select*fromuser_account;+--------+--------+|用户名|钱|+-------+------+|1001|22||1002|30||1003|8||1009|11|+--------+--------+4行在set(0.00sec)共有8个用户有用户名，4个用户有账户余额取出用户名和userid为1003的余额，SQL如下：SELECTi.name,a.moneyFROMuser_infoasiLEFTJOINuser_accountasaONi.userid=a.useridWHEREa.userid=1003;第一步：执行FROM子句对两个表执行笛卡尔积运算。笛卡尔乘积运算后，将返回两个表中所有行的组合。左表user_info有8行，右表user_account有4行。生成的虚拟表vt1为8*4=Line32:SELECT*FROMuser_infoasiLEFTJOINuser_accountasaON1;+--------+------+----------+------+|用户名|姓名|用户名|钱|+--------+------+------+------+|1001|×|100122||1002|是|1001|22||1003|z|1001|22||1004|一个|1001|22||1005|乙|1001|22||1006|1001|22||1008|电子|1001|22||1001|×|1002|30||1002|是|1002|30||1003|z|1002|30||1005|b|1002|30||1006|c|1002|30||1007|d|1002|30||1008|e|100230||1001|×|1003|8||1002|是|1003|8||1003|z|1003|8||1004|8||1007|d|1003|8||1008|电子|1003|8||1001|×|1009|11||1002|11||1004|一个|1009|11||1005|乙|1009|11||1006|c|1009|11||1007|d|------+------+--------+--------+32rowsinset(0.00sec)第2步：执行ON子句过滤掉Rows满足条件ONi.userid=a.userid过滤后vt2如下：+--------+------+--------+-------+|用户名|姓名|用户名|钱|+--------+------+------+------+|1001|×|100122||1002|是|1002|30||1003|z|1003|8|+--------+------+--------+-------+第3步：JOIN添加外部行LEFTJOIN将插入行将vt2中没有出现的左表放入vt2中，每一行的剩余字段将被填充为NULL、RIGHTJOIN是一样的。本例中使用了LEFTJOIN，所以将左表user_info的剩余行加入生成表vt3:+--------+------+--------+------+|用户名|姓名|用户名|钱|+--------+------+------+------+|1001|x|1001|22||1002|y|1002|30||1003|z|1003|8||1004|a|NULL|NULL||1005|b|NULL|空||1006|c|空|空||1007|d|空|空||1008|电子|空|-----+--------+第四步：WHERE条件过滤WHEREa.userid=1003生成表vt4:+--------+------+------+--------+|用户名|姓名|用户名|钱|+--------+------+------+------+|1003|z|1003|8|+------+-----+--------+--------+Step5:SELECTSELECTi.name,a.money生成vt5:+------+------+|姓名|钱|+-----+-------+|z|8|+------+------+虚表vt5作为最终结果返回给客户端。INNER/LEFT/RIGHT/FULLJOININNERJOIN...ON...的区别：返回左右表中所有匹配的行（因为上面只进行了第二步ON过滤，而不执行添加外部行的第三步）LEFTJOIN...ON...：返回左表的所有行。如果某些行在右表中没有对应的匹配行，则将右表的列设置为NULLRIGHTJOIN...ON...：返回右表的所有行，如果某些行在中没有对应的匹配行左表，在新表中将左表的列设置为NULLINNERJOIN以上面第三步添加外部行为例，如果将LEFTJOIN替换为INNERJOIN，则跳过这一步，生成的表vt3与vt2完全相同：+--------+-----+--------+--------+|用户名|姓名|用户名|钱|+--------+------+------+------+|1001|×|1001|22||1002|是|1002|30||1003|z|1003|---+RIGHTJOIN如果将LEFTJOIN换成RIGHTJOIN，生成的表vt3如下：+--------+------+--------+-------+|用户名|姓名|用户名|钱|+--------+------+------+------+|10011001|22||1002|是|1002|30||1003|z|1003|8||空|空|1009|11|+------+-----+--------+------+因为user_account中有一行userid=1009（右表），但是在user_info（左表）中没有这一行的记录，所以它会在第三步插入下面一行：|空|空|1009|11|FULLJOIN上面引用的文章提到了标准SQL定义的FULLJOIN，在mysql中是不支持的，但是我们可以通过LEFTJOIN+UNION+RIGHTJOIN来实现FULLJOIN:SELECT*FROMuser_infoasiRIGHTJOINuser_accountasa.userid=i.useridunionSELECT*FROMuser_infoasiLEFTJOINuser_accountasa.userid=i.userid;他将返回以下结果：+--------+------+--------+------+|用户名|姓名|用户名|钱|+--------+------+--------+--------+|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或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子句，加入externalrowstothevirtualtabletogeneratevt3isthefinalresult:vt2:+--------+------+--------+-------+|用户名|姓名|用户名|钱|+--------+------+------+------+|1003|z|1003|8|+--------+------+--------+------+vt3:+--------+------+--------+-------+|用户名|姓名|用户名|钱|+--------+------+--------+------+|1001|×|空|空||1002|是|空|空||1003|z|1003|8||1004|空|空||1005|乙|空|空||1006|c|空|空||1007|d|------+--------+--------+第二种情况，LEFTJOIN过滤掉了满足i.userid=a.userid的行生成表vt2；然后执行第三步JOIN子句添加外部行生成表vt3；然后执行第四步WHERE子句，然后过滤vt3表生成vt4，得到Finalresult:vt2:+--------+------+--------+-------+|用户名|姓名|用户名|钱|+-------+------+--------+------+|1001|x|1001|22||1002|y|1002|30||1003|z|1003|8|+--------+------+--------+--------+vt3:+---------+------+--------+--------+|用户名|姓名|用户名|钱|+--------+------+--------+--------+|1001|x|1001|22||1002|y|1002|30||1003|z|1003|8||1004|一个|空|空||1005|乙|空|空||1006|c|空|空||1007|d|空|空||---+-------+--------+-------+vt4:+-------+-------+--------+--------+|用户名|姓名|用户名|钱|+--------+------+--------+-------+|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;返回结果为：+--------+------+--------+------+|用户名|姓名|用户名|钱|+--------+------+--------+-------+|1003|z|1003|8|+------+-----+---------+------+参考《MySQL技术内幕：SQL编程》SQLJoins-W3Schoolssql-有什么区别“INNERJOIN”和“OUTERJOIN”？MySQL::MySQL8.0参考手册::13.2.10.2JOIN语法SQL的可视化表示JoinsJoin(SQL)-维基百科)
