Modify SQL query to include cases without a value

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