left join on 条件对左表不起作用???

1,611 阅读2分钟

今天在网上看到一篇博文,提到:on 后面只针对t2表进行过滤,所以t1.id > 1不起作用

image-20210926225120346.png

看完有点懵逼,感觉好像说不过去。于是自己动手试验了下。

首先,复习一下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补上的行
    }         
  }
}

由上面可知:执行过程大致如下:

  1. 遍历左表(LT)的每一行lt,遍历右表(RT)每一行rt, 组合成(lt,rt)。
  2. 如果(lt,rt)满足on的条件P1(lt,rt),去第3步,如果不满足则去第4步。
  3. 判断是否满足where条件P2(lt,rt)。如果满足则输出、
  4. 将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:

image-20210926230639576.png

t2:

image-20210926230707875.png

执行语句:

select * from t1 left join t2 on t1.id = t2.tid

结果:

image-20220119205651071.png

select * from t1 left join t2 on t1.id = t2.tid and t1.id != '1'

image-20210926230844436.png

select * from t1 left join t2 on t1.id = t2.tid and t2.tid != '1'

image-20210926230915226.png

select * from t1 left join t2 on t1.id = t2.tid and t1.id > 1;

image-20210926230933371.png

后面3个的sql结果都是一样。

其中第四个sql就跟一开头说的对左表作用无效矛盾。如果无效那就会跟第1个sql一样

其实对着left join 的执行过程就知道为什么会出现这个结果。原因都是id = 1的左表 拼接后的行(lt, rt)都不满足P1(lt,rt)。因此会把lt将Null拼接成(lt,NULL)输出。