MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Categories (CategoryID int, CategoryName varchar(20))
create table Products (ProductID int, CategoryID int)
insert into Categories values(1, 'Cat1')
insert into Categories values(2, 'Cat2')
insert into Categories values(3, 'Cat3')
insert into Products values(1, 1)
SELECT C.CategoryID, CategoryName, COUNT(ProductID) as Cnt
FROM Categories C 
LEFT JOIN Products P ON C.CategoryID = P.CategoryID
GROUP BY C.CategoryID, CategoryName


CategoryID
CategoryName
Cnt


1
Cat1
1

2
Cat2
0

3
Cat3
0


SELECT P.CategoryID, CategoryName, COUNT(ProductID) as Cnt
FROM Products P 
LEFT JOIN Categories C ON P.CategoryID = C.CategoryID
GROUP BY CategoryName, P.CategoryID


CategoryID
CategoryName
Cnt


1
Cat1
1