skip to Main Content

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

2

Answers


  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

    Login or Signup to reply.
  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.
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search