mySQL AND和Where的区别是什么?

Question

我正在使用mysql，我混淆了" and "， "Where"谁能告诉我这两者有什么区别?

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

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

Answer 1

它们是不一样的，第一个将返回所有的关联，第二个将只针对匹配的行。

在另一个重复的问题中，你可以看到完整的解释和例子

SQL JOIN - WHERE子句vs. ON子句

Answer 2

只需将查询更改为使用内部连接，如下所示:

select tableA.id, tableA.name, tableB.details 
from tableA
inner join tableB ...

下面是左连接的定义:

The LEFT JOIN (also called LEFT OUTER JOIN) keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

，而内部连接的定义是:

The INNER JOIN keyword return rows when there is at least one match in both tables.