MySQL COUNT and IFNULL

I have tables like this: SQLFIDDLE

I’m trying to count how many products are in each category

SELECT `category_id`, `category_name`, IFNULL(count(*),0) cat_stat 
FROM 
    `product_to_categories`
NATURAL JOIN 
    `categories`
GROUP BY `category_id`

it is almost working except IFNULL part.
I recieve:

+-------------+---------------+----------+
| category_id | category_name | cat_stat |
+-------------+---------------+----------+
| 1           | category1     | 5        |
+-------------+---------------+----------+
| 2           | category2     | 5        |
+-------------+---------------+----------+
| 3           | category3     | 6        |
+-------------+---------------+----------+
| 5           | category5     | 4        |
+-------------+---------------+----------+

but i would also like to get null results:

+-------------+---------------+----------+
| category_id | category_name | cat_stat |
+-------------+---------------+----------+
| 1           | category1     | 5        |
+-------------+---------------+----------+
| 2           | category2     | 5        |
+-------------+---------------+----------+
| 3           | category3     | 6        |
+-------------+---------------+----------+
| 4           | category4     | 0        |
+-------------+---------------+----------+
| 5           | category5     | 4        |
+-------------+---------------+----------+

thanks!

Answer

NATURAL JOIN == NATURAL INNER JOIN whereas you need OUTER JOIN.
Base table is categories, so it must be mentioned first (NATURAL LEFT JOIN takes the values for common columns from the most left table).
You must count definite column, not common column or total rows amount (they will give 1, not 0).

SELECT `category_id`, 
       `category_name`, 
       COUNT(`product_to_categories`.`category_id`) cat_stat 
FROM `categories`
NATURAL LEFT JOIN `product_to_categories`
GROUP BY `category_id`

Advertisement

Answer