美文网首页
MySQL的Left、Right join(外连接)中的on条件

MySQL的Left、Right join(外连接)中的on条件

作者: 后厂村村长 | 来源:发表于2021-08-13 14:38 被阅读0次

数据库通过JOIN连接两张或多张表返回记录时,都会生成一张中间的临时表,再将这张临时表返给用户。
对主表的筛选条件,应放在 where 条件后,如果在 on 之后,则对主表筛选来说,没有任何意义。

on、where 执行顺序

on的优先级,高于where
两者后面放置相同条件,可能会导致结果集不同,就是因为优先级所致。

假设有表,
table1:

id num
1 111
2 222

table2:

num name
111 first
222 second

执行如下两条查询:

1、select a.id,a.num,b.name from table1 a left join table2 b on (a.num = b.num and b.name='first');
2、select a.id,a.num,b.name from table1 a left join table2 b on (a.num = b.num) where b.name='first';

则,第1条SQL返回:

id num name
1 111 first
2 222 (Null)

第2条SQL返回:

id num name
1 111 first

第一条sql的执行流程:

首先找到b表的name为first的记录行on (a.num = b.num and b.name='first'),然后找到a的全部数据(left join),生成临时表返回数据。

第二个sql的执行流程:

先生成临时表(全量),然后执行where过滤b.name=’first’不为真的结果,最后返回数据。

因为on会首先过滤掉不符合条件的行,然后才会进行其它运算。
执行顺序: on > where > having, 因此理论上on是最快的。

多表查询时,on比where更早起作用。系统首先根据各个表之间的联接条件,把多个表整合成一个临时表,再由where进行过滤,之后再进行计算,计算完后再由having过滤。

对于JOIN参与的表的关联操作,如果需要不满足连接条件的行也被返回,就必需把连接条件放在ON后,不能放在WHERE后面,如果把连接条件放在了WHERE后面,那么所有的LEFT、RIGHT等操作将不起任何作用,对于这种情况,它的效果就完全等同于INNER连接。

结论

on是在生成临时表的时候使用的条件,不管on的条件是否起到作用,都会返回左表 (主表) 的行。
where则是在生成临时表之后使用的条件,此时已经不管是否使用了left join了,只要条件不为真的行,全部过滤。

Tips:

所有的join条件必需放在ON后面。
否则前面的LEFT、RIGHT关联将作为摆设,不起任何作用。

!!!

如果同时存在多个left join on,请注意on后面的条件与哪个表关联。
这一点很重要!!!
例如存在表A,B,C:
A left join B on A.x = B.x left join C on A.x = C.x;
B、C都与A建立了关联,但B和C之间是没有任何数据关联的。

但是,如果把A.x = C.x改成B.x = C.x,那么B和C的表数据会先建立关联并过滤数据,之后再与A表数据进行关联,可能会出现数据丢失。

相关文章

网友评论

      本文标题:MySQL的Left、Right join(外连接)中的on条件

      本文链接:https://www.haomeiwen.com/subject/flirbltx.html