背景:在一次开发中,查询出现了2条重复数据。数据来源于2张表的不同字段,使用左连接方式连表。在这之前,对于左连接的印象始终停留在:以左边(x left join y ) 即x 的表为最终结果为基础,右边取需要的字段,如果右边符合条件的数据行的字段为空,则取空。 直到出现了以上现象:左连接出现了2条重复数据,颠覆了我的想象。。
测试:
环境:mysql 5.5.62
1:新建2张表做测试
user: 未建立显示主键 (使用默认主键),加入3条数据
depart:未建立显示主键(使用默认主键),插入2条数据
2:测试左连接,不带查询条件
语句: SELECT * FROM user u left join depart d on u.id=d.id
之前的理解:最终结果最多只有 2 条,即以user为最终结果,至于右边,2条数据的id都匹配左边的一条数据,额,还真没细想。
实际执行结果:
分析:得到 3条数据。id为2的没什么好说,重点关注id为1的。左边id为1的数据为1条,右边有2条,left join 后 变为2条id为1的数据。最终为3条。推测 left join 的结果行数为:左表不符合连接条件的数据行+左表符合连接条件行*右表符合连接条件行
进一步验证:将左边user 的数据添加一行数据
再次执行 :SELECT * FROM user u left join depart d on u.id=d.id
执行结果:
验证分析 : 左边不符合连接条件的行:1条,符合连接条件的行:2行。右边符合连接条件的行:2行,带入公式:1+2*2=5。正解。
结论:推测 left join 的结果行数为:左表不符合连接条件的数据行+左表符合连接条件行*右表符合连接条件行
3:测试带查询条件的左连接,这里分为2种场景。
user表的数据:
depart表的数据:
场景一: 直接在on 后面再加条件
SELECT * FROM user u left join depart d on u.id=d.id and d.dep_name='开发'
执行结果:
分析:直接在on 后面 加 and 条件,相当于将and 条件当作连接条件,根据第一步验证的左连接公式 :左表不符合连接条件的数据行+左表符合连接条件行*右表符合连接条件行。 在这里,左表不符合连接条件的行数为 1条,id=2(关羽),左表符合连接条件的行数为2条,右表 符合连接条件的只有 1条,(清算那条数据被 and d.dep_name='开发' 过滤掉了),最终结果 1+2*1=3,正解。
场景二:在where 后面加条件
SELECT * FROM user u left join depart d on u.id=d.id where d.dep_name='开发'
执行结果:
分析:左表不符合连接条件的行数为 1条,id=2(关羽),左表符合连接条件的行数为2条,右边表符合连接条件的有 2条,左连接查询后:1+2*2 = 5 条。后面还有 where 条件, where d.dep_name='开发' ,最终只剩下2条(需要和场景一区分)。
根据 以上结果猜测:mysql 先执行 左连接,然后 在左连接的结果内进行 where 条件过滤 (待验证)