解决Mysql的left join无效及使用的注意事项说明
2021 年 7 月 1 日 10 时 05 分进行了更新，作者是栗子木。
这篇文章主要介绍了关于解决 Mysql 的 left join 无效的相关内容以及使用时的注意事项，这些内容具有很好的参考价值，希望能对大家有所帮助。倘若存在错误或者未考虑周全的地方，希望大家不吝赐教。
Mysql的left join无效及使用
今天写 sql 时发现，使用 left join 并没有将左边表的所有数据都查询出来，这让我郁闷了一段时间。之后我仔细研究了一会儿，才意识到自己犯了一个常识性的错误（我是新手）。
这是原sql
这样的查询无法将 tb_line 这张表的所有数据都查询出来，真的很尴尬……
之后我才知晓，当我们进行多表查询时，在执行到 where 之前，会首先形成一个临时表。
on 是临时表中的条件筛选。使用 left join 时，无论条件是否为真，都会查询出左边表的数据。如果条件为假，就会显示为 null。
where则是在临时表生成之后的过滤条件
在第一张图里，我把 tb_vehicle 这张表的过滤条件置于 where 之中。这样一来，left join 所产生的条件为假的数据，会在 where 的 v.del_flag='0'中被过滤掉，原因是条件为假的数据，其 del_flag 都为空。
所以我看上去使用了 left join 。实际上，这样的写法所得到的结果和使用 inner join 是相同的。
正确sql如下：
在临时表中就做好条件筛选，这样就能够得到左边表的数据
总结：
在使用 left join 并且需要进行条件查询时，需要谨慎考虑该条件筛选究竟放在 on 后面还是 where 后面。
Mysql left join 避坑指南现象
在我们使用 mysql 查询时，left join 非常常见。例如，博客里一篇文章的评论数量、商城里一个货物的评论数量、一条评论的点赞数量等情况。然而，由于对 join、on、where 等关键字不熟悉，有时会致使查询结果与预期不同。所以，今天我来进行总结，一起避免踩坑。
这里先给出一个场景，接着抛出两个问题。倘若你都能答对，那么这篇文章就无需再看了。
假设有一个班级管理应用，存在一个名为 classes 的表，其中存了所有的班级；还有一个名为 students 的表，里面存了所有的学生，其具体数据情况如下：
SELECT * FROM classes;
SELECT * FROM students;
那么现在有两个需求：
找出每个班级的名称及其对应的女同学数量
找出一班的同学总数
对于需求 1，大多数人在未经思考的情况下就能想出以下两种 sql 写法。请问，这两种写法中哪一种是正确的？
选择 c 的 name，将 s 的 name 进行计数并命名为 num 。
使用左连接（left join）将 classes 表 c 和 students 表 s 进行连接操作。
on s.class_id = c.id
and s.gender = 'F'
group by c.name
或者
SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
where s.gender = 'F'
group by c.name
对于需求 2，大多数人能够不假思索地想出以下两种 SQL 写法。请问，这两种写法哪一种是正确的？
SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
where c.name = '一班'
group by c.name
或者
SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
and c.name = '一班'
group by c.name
不要继续往下翻啦！先给出你自己的答案，因为正确答案就在下面呢。
答案有两个需求，其中第一条语句是正确的。要弄清楚这个问题，就必须明白 mysql 对于 left join 的执行原理，下节将对此展开。
根源
mysql 在处理 left join 时采用类似嵌套循环的方式。以下面的语句为例：
从 LT 表中选择所有数据，然后将其与 RT 表进行左连接，连接条件是 P1(LT,RT)，最后筛选出满足 P2(LT,RT)条件的数据。
其中一个过滤条件是 P1 且为 on 条件，若缺失则视为 TRUE；另一个过滤条件是 P2 且为 where 条件，若缺失也视为 TRUE。该语句的执行逻辑可描述为：先判断 P1 条件（若有则为 on 条件，若无视为 TRUE），再判断 P2 条件（若有则为 where 条件，若无视为 TRUE）。
对于左表的每一行 lt，进行如下操作：
BOOL b = FALSE;
对于 RT 中的每一行 rt 而言，如果 P1(lt, rt)成立，那么就进行以下操作：// 遍历右表每一行，找到满足 join 条件的行
如果 P2 小于 rt 且 P2 大于 lt ，那么就满足 where 过滤条件 。
t:=lt||rt;//合并行，输出该行
}
b=TRUE;// lt在RT中有对应的行
}
如果 b 不成立，即遍历完 RT 后，发现 lt 在 RT 中没有对应的行，那么就尝试用 null 补一行。
如果 P2 小于某个值且该值为 NULL 时满足 where 过滤条件；
t:=lt||NULL; // 输出lt和null补上的行
}
}
}
在实际情况里，MySQL 会通过使用 buffer 的方式来进行优化，以此减少行比较的次数。然而，这并不会对关键的执行流程产生影响，并且也不在本文的讨论范围之内。
从这个伪代码中，我们可以看出两点：
如果要对右表进行限制，那就必须在 on 条件中进行。要是在 where 中进行，就可能会使数据缺失，从而让左表中在右表中没有匹配行的那些行在最终结果中不出现，这违背了我们对于 left join 的理解。对于左表无右表匹配行的那些行来说，在遍历右表之后，b 为 FALSE。于是会尝试用 NULL 来补齐右表。然而，此时我们的 P2 对右表行进行了限制，由于 NULL 通常都不会满足限制条件（除非是 IS NULL 这种情况），所以如果 NULL 不满足 P2，就不会被加入到最终的结果中，从而导致结果缺失。
如果不存在 where 条件，不管 on 条件怎样对左表进行限制，左表的每一行都至少会有一行合成结果。对于左表的行来说，倘若右表没有对应的行，那么在右表遍历结束后，b 为 FALSE，此时会用一行 NULL 来生成数据，而这部分数据是多余的。因此，必须使用 where 对左表进行过滤。
下面展开两个需求的错误语句的执行结果和错误原因：
需求1
需求2
需求 1 因为在 where 条件里对右表进行了限制，所以导致了数据缺失，具体来说，四班本应该有一个结果为 0 的情况。
需求 2 由于在 on 条件中对左表进行了限制，所以导致数据出现多余的情况，即其他班的结果也被显示出来了，而且这些结果还是错误的。
总结
通过上面的问题现象及分析，得出结论：在 left join 语句里，左表的过滤需放在 where 条件中，右表的过滤需放在 on 条件中，如此一来，结果才能不多不少且刚刚好。
SQL 看起来较为简单，然而实际上其中蕴含着诸多细节原理。哪怕只是一个小小的混淆，都可能致使结果与预期不一致。因此，在平时应当留意这些细节原理，以防在关键时候出现错误。
这是个人的经验，希望能给大家提供参考，同时也希望大家多多支持脚本之家。
本文来自作者[qulangwang]投稿，不代表趣浪号立场，如若转载，请注明出处：https://qulangwang.cn/life/202504-15544.html
评论列表（4条）
我是趣浪号的签约作者“qulangwang”！
希望本篇文章《2021年必看！Mysql的left join无效问题剖析及使用详解》能对你有所帮助！
本站[趣浪号]内容主要涵盖：生活百科,小常识,生活小窍门,知识分享
本文概览：这篇文章主要介绍了解决Mysql的left join无效及使用的注意事项说明，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...