今天在网上看到一篇博文,提到:on 后面只针对t2表进行过滤,所以t1.id > 1不起作用
看完有点懵逼,感觉好像说不过去。于是自己动手试验了下。
首先,复习一下left join 的执行原理
mysql 对于left join
的采用类似嵌套循环的方式来进行从处理,以下面的语句为例:
SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)
伪代码:
FOR each row lt in LT {// 遍历左表的每一行
BOOL b = FALSE;
FOR each row rt in RT such that P1(lt, rt) {// 遍历右表每一行,找到满足join条件的行
IF P2(lt, rt) {//满足 where 过滤条件
t:=lt||rt;//合并行,输出该行
}
b=TRUE;// lt在RT中有对应的行
}
IF (!b) { // 遍历完RT,发现lt在RT中没有有对应的行,则尝试用null补一行
IF P2(lt,NULL) {// 补上null后满足 where 过滤条件
t:=lt||NULL; // 输出lt和null补上的行
}
}
}
由上面可知:执行过程大致如下:
- 遍历左表(LT)的每一行lt,遍历右表(RT)每一行rt, 组合成(lt,rt)。
- 如果(lt,rt)满足on的条件P1(lt,rt),去第3步,如果不满足则去第4步。
- 判断是否满足where条件P2(lt,rt)。如果满足则输出、
- 将lt和Null拼成(lt,NULL)。如果满足where条件P2(lt,NULL)则输出。
由上面的执行步骤,我们可以推出以下结论:
1.如果想对右表进行限制,则一定要在on
条件中进行
2.对左表进行过滤用where
然后,在mysql中生成以下两个表
CREATE TABLE `t1` (
`id` decimal(10,0) DEFAULT NULL,
`v1` varchar(100) DEFAULT NULL,
`v11` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
CREATE TABLE `t2` (
`tid` decimal(10,0) DEFAULT NULL,
`v2` varchar(100) DEFAULT NULL,
`v22` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
两表分别有以下数据:
t1:
t2:
执行语句:
select * from t1 left join t2 on t1.id = t2.tid
结果:
select * from t1 left join t2 on t1.id = t2.tid and t1.id != '1'
select * from t1 left join t2 on t1.id = t2.tid and t2.tid != '1'
select * from t1 left join t2 on t1.id = t2.tid and t1.id > 1;
后面3个的sql结果都是一样。
其中第四个sql就跟一开头说的对左表作用无效矛盾。如果无效那就会跟第1个sql一样
其实对着left join 的执行过程就知道为什么会出现这个结果。原因都是id = 1的左表 拼接后的行(lt, rt)都不满足P1(lt,rt)。因此会把lt将Null拼接成(lt,NULL)输出。