• Mysql连接查询时查询条件放在On之后和Where之后的区别


    背景

    在一次对数据进行统计的时候,需要对两张表进行关联,类似于这样的语句a left join b on a.id = b.id where b.name = xx。发现最终的结果和预期不一致,汇总之后的数据变少了。

    一开始还比较费解,后面回过神来才发现,犯了一个低级的错误,就是在使用left join时过滤条件放到on后面还是where后面是有区别的,如果没有搞清楚他们的区别,连表汇总的结果就会变少或者变多。

    探究

    利用廖雪峰提供的在线工具,利用student表和classes表我们做一个测试,

    student表

     classes表

    1.统计每个班级中女生的数量

    问题SQL

    select a.name, count(b.name) as num from classes a left join students b 
    on a.id = b.class_id
    where b.gender = 'F'
    group by a.name
    

     查询结果

     正确的写法

    select a.name, count(b.name) as num 
    from classes a left join students b 
    on a.id = b.class_id and b.gender = 'F'
    group by a.name
    

    查询结果

    2: 只统计‘一班’的学生数量

    错误的写法

    select a.name, count(b.name) as num 
    from classes a left join students b 
    on a.id = b.class_id and a.name = '一班'
    group by a.name 

    查询结果

     正确的写法

    select a.name, count(b.name) as num 
    from classes a left join students b 
    on a.id = b.class_id 
    where a.name = '一班'
    group by a.name 

    查询结果

     原因

     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。

    问题一错误的原因:由于在where条件中对右表限制,导致数据缺失(四班应该有个为0的结果)

    问题二错误的原因:由于在on条件中对左表限制,导致数据多余(其他班的结果也出来了,还是错的)。on 后跟关联表(从表)的过滤条件,如果再加筛选条件只针对关联表!

    on 后跟关联表(从表)的过滤条件,where 后跟主表或临时表的筛选条件(左连接为例,主表的数据都会查询到,所以临时表中必定包含主表所有的字段,需要给主表加什么筛选条件,直接给临时表加效果相同) 

    总结

    通过上面的问题现象和分析,可以得出了结论:在left join语句中,左表过滤必须放where条件中,右表过滤必须放on条件中,这样结果才能不多不少,刚刚好。

    SQL 看似简单,其实也有很多细节原理在里面,一个小小的混淆就会造成结果与预期不符,所以平时要注意这些细节原理,避免关键时候出错。 

    本篇文章如有帮助到您,请给「翎野君」点个赞,感谢您的支持。

    首发链接:https://www.cnblogs.com/lingyejun/p/16380106.html

  • 相关阅读:
    冒泡排序
    对于Random.Next()生成随机数重复的问题
    二分法查找
    工厂方法模式
    简单的工厂模式
    unity AssetBundle打包及加载
    关于谷歌不然安装除商店之外的拓展应用解决
    【Unity与23种设计模式】抽象工厂模式(Abstract Factory)
    【Unity与23种设计模式】解释器模式(Interpreter)
    【Unity与23种设计模式】原型模式(Prototype)
  • 原文地址:https://www.cnblogs.com/lingyejun/p/16380106.html
Copyright © 2020-2023  润新知