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

原始数据

mysql> select * from t1;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    1 |
|    2 |    2 |
|    3 |    1 |
|    3 |    2 |
|    1 |    2 |
|    4 |    4 |
+------+------+
8 rows in set (0.06 sec)

mysql> select * from t2;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    1 |
|    4 |    4 |
|    5 |    5 |
|    1 |    2 |
+------+------+
4 rows in set (0.06 sec)

on后跟条件和where中条件的区别

mysql> select * from t1 left join t2 on t1.c1=t2.c1 and t2.c1 = 1;
+------+------+------+------+
| c1   | c2   | c1   | c2   |
+------+------+------+------+
|    1 |    1 |    1 |    2 |
|    1 |    1 |    1 |    1 |
|    1 |    2 |    1 |    2 |
|    1 |    2 |    1 |    1 |
|    2 |    1 | NULL | NULL |
|    2 |    2 | NULL | NULL |
|    3 |    1 | NULL | NULL |
|    3 |    2 | NULL | NULL |
|    1 |    2 |    1 |    2 |
|    1 |    2 |    1 |    1 |
|    4 |    4 | NULL | NULL |
+------+------+------+------+
11 rows in set (0.17 sec)

mysql> select * from t1 left join t2 on t1.c1=t2.c1 where t2.c1 = 1;
+------+------+------+------+
| c1   | c2   | c1   | c2   |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
|    1 |    1 |    1 |    2 |
|    1 |    2 |    1 |    1 |
|    1 |    2 |    1 |    2 |
|    1 |    2 |    1 |    1 |
|    1 |    2 |    1 |    2 |
+------+------+------+------+
6 rows in set (0.07 sec)

如果想对右表进行限制，则一定要在on条件中进行，若在where中进行则可能导致数据缺失，导致左表在右表中无匹配行的行在最终结果中不出现，违背了我们对left join的理解。因为对左表无右表匹配行的行而言，遍历右表后b=FALSE,所以会尝试用NULL补齐右表，但是此时我们的P2对右表行进行了限制，NULL若不满足P2(NULL一般都不会满足限制条件，除非IS NULL这种)，则不会加入最终的结果中，导致结果缺失。
如果没有where条件，无论on条件对左表进行怎样的限制，左表的每一行都至少会有一行的合成结果，对左表行而言，若右表若没有对应的行，则右表遍历结束后b=FALSE，会用一行NULL来生成数据，而这个数据是多余的。所以对左表进行过滤必须用where。
使用where条件则等同于left join变成了inner join，对inner join而言，on条件和where条件等价，生成的结果集一致

mysql> select * from t1 inner join t2 on t1.c1=t2.c1 and t2.c1 = 1;
+------+------+------+------+
| c1   | c2   | c1   | c2   |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
|    1 |    1 |    1 |    2 |
|    1 |    2 |    1 |    1 |
|    1 |    2 |    1 |    2 |
|    1 |    2 |    1 |    1 |
|    1 |    2 |    1 |    2 |
+------+------+------+------+
6 rows in set (0.07 sec)

mysql> select * from t1 inner join t2 on t1.c1=t2.c1 where t2.c1 = 1;
+------+------+------+------+
| c1   | c2   | c1   | c2   |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
|    1 |    1 |    1 |    2 |
|    1 |    2 |    1 |    1 |
|    1 |    2 |    1 |    2 |
|    1 |    2 |    1 |    1 |
|    1 |    2 |    1 |    2 |
+------+------+------+------+
6 rows in set (0.19 sec)

如果想保留left join，并希望缩小右表扫描是数据量，需要在join后的结果集中删除补齐的null的数据

mysql> select * from t1 left join t2 on t1.c1=t2.c1 and t2.c1 = 1 where t2.c1 is not null;
+------+------+------+------+
| c1   | c2   | c1   | c2   |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
|    1 |    1 |    1 |    2 |
|    1 |    2 |    1 |    1 |
|    1 |    2 |    1 |    2 |
|    1 |    2 |    1 |    1 |
|    1 |    2 |    1 |    2 |
+------+------+------+------+
6 rows in set (0.07 sec)

mysql> select * from t1 left join (select * from t2 where t2.c1=1) t3 on t1.c1 = t3.c1 where t3.c1 is not null;
+------+------+------+------+
| c1   | c2   | c1   | c2   |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
|    1 |    1 |    1 |    2 |
|    1 |    2 |    1 |    1 |
|    1 |    2 |    1 |    2 |
|    1 |    2 |    1 |    1 |
|    1 |    2 |    1 |    2 |
+------+------+------+------+
6 rows in set (0.07 sec)

Left join实现机制

Left join实现机制

CATALOG

FEATURED TAGS

FRIENDS