Modify SQL query to include cases without a value

Question

I have this database assignment where I need to write a query "to display the id and name of each category, with the number of products that belong to the category". I was able to solve it and used this query.

SELECT Category.Id, Category.Name, COUNT(Category.Name) 
FROM Category, Product 
WHERE (CategoryId = Category.Id) 
GROUP BY Category.Id;

But I want to modify it to make all categories appear, even those with no products. Stuck on this part. Any help is appreciated.

Answer 1

You can left join:

select c.id, c.name, count(p.categoryid) cnt_products
from category c
left join product p on p.categoryid = c.id
group by c.id;

A correlated subquery is also a fine solution, which avoids outer aggregation:

select c.*,
    (select count(*) from product p where p.categoryid = c.id) cnt_products
from category c