MySql之leftjoin注意点
left join在我们使⽤mysql查询的过程中可谓⾮常常见，⽐如博客⾥⼀篇⽂章有多少条评论、商城⾥⼀个货物有多少评论、⼀条评论有多少个赞等等。但是由于对join、on、where等关键字的不熟悉，有时候会导致查询结果与预期不符。
假设有⼀个表classes，存了所有的班级；有⼀个表students，存了所有的学⽣，
具体数据如下：
那么现在有两个需求：
1、出每个班级的名称及其对应的⼥同学数量
（1）使⽤AND（正确）
SELECT classes.`name`,COUNT(students.`name`)
FROM classes
LEFT JOIN
students
ON classes.`id`=students.`class_id`
AND students.`gender`='F'
GROUP BY classes.`name`
（2）使⽤where（错误）
SELECT classes.`name`,COUNT(students.`name`) FROM classes
LEFT JOIN
students
ON classes.`id`=students.`class_id`
WHERE students.`gender`='F'
GROUP BY classes.`name`
2、出⼀班的同学总数
（1）使⽤AND（错误）
SELECT classes.`name`,COUNT(students.`name`) FROM classes
LEFT JOIN
students
ON classes.`id`=students.`class_id`
AND classes.id=1
GROUP BY classes.`name`
（2）使⽤where（正确）
SELECT classes.`name`,COUNT(students.`name`) FROM classes多表left join
LEFT JOIN
students
ON classes.`id`=students.`class_id`
WHERE classes.id=1
GROUP BY classes.`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补上的⾏
}
}
}
从这个伪代码中，我们可以看出两点：
1、如果想对右表进⾏限制，则⼀定要在on条件中进⾏，若在where中进⾏则可能导致数据缺失，导致左表在右表中⽆匹配⾏的⾏在最终结果中不出现，违背了我们对left join的理解。因为对左表⽆右表匹配⾏的⾏⽽⾔，遍历右表后b=FALSE，所以会尝试⽤NULL补齐右表，但是此时我们的P2对右表⾏进⾏了限制，NULL若不满⾜P2(NULL⼀般都不会满⾜限制条件，除⾮IS NULL这种)，则不会加⼊最终的结果中，导致结果缺失。
2、如果没有where条件，⽆论on条件对左表进⾏怎样的限制，左表的每⼀⾏都⾄少会有⼀⾏的合成结果，对左表⾏⽽⾔，若右表若没有对应的⾏，则右表遍历结束后b=FALSE，会⽤⼀⾏NULL来⽣成数据，⽽这个数据是多余的。所以对左表进⾏过滤必须⽤where。
可以得出了结论：
在left join语句中，左表过滤必须放where条件中，右表过滤必须放on条件中，这样结果才能不多不少，刚刚好。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。