leftjoin的避坑指南

简单记录一下关于leftjoin查询的情况下查询条件到底写在where里面还是on里面的问题。

首先，leftjoin的执行流程如下：

SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)

 

其中P1是on过滤条件，缺失则认为是TRUE，P2是where过滤条件，缺失也认为是TRUE，该语句的执行逻辑可以描述为:

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补上的行 }
} }

从这个伪代码块中可以看出：

如果想对右表进行限制，则一定要在on条件中进行，若在where中进行则可能导致数据缺失，导致左表在右表中无匹配行的行在最终 结果中不出现，违背了我们对left join的理解。因为对左表无右表匹配行的行而言，遍历右表后b=FALSE,所以会尝试用NULL补⻬右 表，但是此时我们的P2对右表行进行了限制，NULL若不满足P2(NULL一般都不会满足限制条件，除非IS NULL这种)，则不会加入最终 的结果中，导致结果缺失。

如果没有where条件，无论on条件对左表进行怎样的限制，左表的每一行都至少会有一行的合成结果，对左表行而言，若右表若没有 对应的行，则右表遍历结束后b=FALSE，会用一行NULL来生成数据，而这个数据是多余的。所以对左表进行过滤必须用where。

 

 

一句话：对右表限制写在on中，对左表过滤写在where中。

where过滤右表会造成数据缺失，on限制左表会造成数据冗余（这是本次博客的起因，多表联查时左表的限制条件放在on条件后，造成了全表扫描，没走索引，导致整个查询sql耗时过长）