What is the difference in mySQL AND vs Where

Question

I'm using mysql and I confused with "And", "Where" Somby dy can tell me what is difference between these.

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

They are not the same, first one will return the associations for all, and the second will do it just for the rows in the where match.

In this other duplicate question you can see the full explanation and examples

SQL JOIN - WHERE clause vs. ON clause

Answer 2

Simply change the query to use an inner join like this:

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

here is the definition of left join:

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).

whereas the definition of the inner join is:

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