MySQL中LEFT JOIN里AND与WHERE的区别是什么？

第一段SQL的行为 #

SELECT *,COUNT(comment.id) as comment_count from posts LEFT JOIN comment on posts.post_id =comment.post_id AND comment.approve = 1 GROUP BY posts.post_id

这里的AND comment.approve = 1是关联条件的一部分，在LEFT JOIN匹配关联表数据时就生效：

• 先筛选出comment表中approve=1的记录，再和posts表按post_id关联
• 无论posts表的记录有没有匹配到符合条件的comment，所有posts记录都会被保留
• 没有匹配到的posts记录对应的comment字段全为NULL，COUNT(comment.id)会统计为0

第二段SQL的行为 #

SELECT *,COUNT(comment.id) as comment_count from posts LEFT JOIN comment on posts.post_id =comment.post_id WHERE comment.approve = 1 GROUP BY posts.post_id

这里的WHERE comment.approve = 1是结果集过滤条件，在LEFT JOIN完成后才生效：

• 先执行LEFT JOIN，把所有posts记录和对应的comment记录（不管approve状态）关联起来
• 之后过滤掉comment.approve != 1的记录——而LEFT JOIN中没有匹配到comment的posts记录，comment.approve是NULL，NULL和1不相等，所以这些posts记录会被直接剔除
• 最终结果只保留那些至少有一条approve=1评论的posts记录

本质差异 #

• ON子句的AND是关联时过滤关联表数据，不影响主表（LEFT JOIN左边的posts）的记录保留
• WHERE子句是关联完成后过滤整个结果集，会剔除所有不满足条件的记录，包括主表中没有匹配到符合条件关联数据的记录

内容的提问来源于stack exchange，提问作者u LoV