关于left join

前言

join是SQL查询中非常常见的一种操作，具体来说有join.left join,right join ,full join等

image.png

left join 之后记录有多少条?

t_name 表

image.png

t_age表

image.png

一对一

image.png

最终以左表的记录为主，t_age有三条数据关联不上，所以显示为空

一对多,多对多

image.png

返回以t_age表为基准，但是结果并不是原来的3条数据，而是7条。比如A表是用户表,B表是订单表，一个用户可能下多个订单i。join之后假设A表匹配50个用户id,B表订单为500个,显然join之后的数据大于50条。这样也是合理的关系。

如何解决一对多的关系

如果你的业务逻辑显示是一对多关系，但是表关系确实是一对多关系。就像订单表和物流表一样。如何解决呢?
可以使用groupby 或者distinct来解决

image.png
image.png

group by 和distinct区别?

单纯的去重操作使用distinct，速度是快于group by的。

• distinct需要将col列中的全部内容都存储在一个内存中，可以理解为一个hash结构,key为col的值,最后计算hash结构中有多少个key即可得到结果。很明显，需要将所有不同的值都存起来。内存消耗可能较大。
• group by是将col排序。数据库中的group一般使用sort方法，即数据库会先对col排序，然后单纯的计数即可。有点是空间复杂度小，缺点是需要进行一次排序，执行时间较长。

left join 的执行原理

mysql 对于left join的采用类似嵌套循环的方式来进行从处理，以下面的语句为例：

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。