MySQL count does not return 0 if no record found

Question

Although I researched on this topic and came across a few solutions like using JOIN LEFT or subqueries, I am still unable to get the result I want as I am not strong in mySQL. I am more of a web designer trying to use simple php to my website better for a school project.

I am trying to create a web application something similar to a blog. I wanted to count how many comments are there for a post and display the number for my users to see, but if there is no comment for that row, my query will return nothing instead of 0.

This is my query below:

SELECT post.post_id, COUNT(comment) 
FROM `comment`, post 
WHERE `comment`.post_id = post.post_id 
GROUP BY post.post_id

The result:

Record | post_id | COUNT(comment)
1      | 12      | 2
2      | 13      | 1
3      | 15      | 1
4      | 16      | 1

As you can see, post_id 14 has no comments, thus my query returns nothing. What must I do to make my result looks like this?

Record | post_id | COUNT(comment)
1      | 12      | 2
2      | 13      | 1
3      | 14      | 0
4      | 15      | 1
5      | 16      | 1

Also, it would be nice of you guys to give me references or links to understand the concept behind the solution as I want to learn more about php :)

Answer 1

So Actually when you do that (which is what you do, reformulated for the JOIN):

SELECT post.post_id, COUNT(comment) 
FROM `comment`
INNER JOIN post ON `comment`.post_id = post.post_id 
GROUP BY post.post_id;

You gather only post rows having at least one reference in comment.

If you alter the JOIN type to a LEFT join, this way:

SELECT post.post_id, COUNT(comment) 
FROM `comment`
LEFT JOIN post ON `comment`.post_id = post.post_id 
GROUP BY post.post_id;

Then the rows from post are all there, and NULL values are inserted for columns of comments if no comments related to this row exists (that's a left join). So if comment is a column from table comment it will be there for each rows of post table, but with a NULL value, after the group by on the post_id column the subset of comments related to this post contains only 1 NULL value, the count should return 0.

select count(NULL);

returns 0.

Now you could use a subquery but that's a really bad idea, subqueries are usually done instead of LEFT JOINS, usually it's a mistake, sometimes it's not, but it's really often a mistake. When you do a left join indexes are used to compare the key values of the 2 tables (the ON clause) and build one final 'temporary' result of rows, mixing values from both tables (and then, or maybe in the same time, the filters from other parts of your queries are applied). When you use a subquery, for each row of the first table a new query is run to get results from the second table (not always, but it's another problem), the cost is reeeaaally bigger for the database engine.

Answer 2

Query the post table and do a subquery for the count on the comments query.

SELECT post.post_id, (SELECT COUNT(comment) FROM `comment` WHERE `comment`.post_id = post.post_id) as comments FROM post

This may get extremely slow with lots or rows so add a limit with a pager when you get to that point.