Om man använder mysql är subquerys dumt (eftersom det bara stöds i de senaste versionerna och mysqlinstallationer har en tendens att inte vara helt up-to-date), men annars är det ju ett utmärkt sätt att låta databasen göra jobbet istf att göra det själv i kod.
Dock är väl din fråga knappast något som verkligen kräver en subquery... Det gör den bara onödigt komplicerad.
Kod:
SELECT c.category_id, c.category_name, count(*)
FROM shop_category c, shop_product p
WHERE c.category_id = p.category_id
GROUP BY c.category_id, c.category_name
Det är ju bara listan i group by som måste innehålla alla kolumner från shop_category du skall plocka ut (och sluta göra select * - det är en dålig ovana - ange alltid vad du vill ha).
Min query blir dessutom sannolikt snabbare eftersom den slipper göra en subquery.