法一：

这种题目说实话我写的是比较蛋疼的，所以都会记录下来

先贴出来我写的代码：死活不愿意用prod_name来group by，那么就要多投影一次

select res.prod_name, res.total as orders
from
(
  	select t1.prod_id, t1.prod_name, count(t2.order_num) as total
	from Products as t1
    left join 
    OrderItems as t2
    on t1.prod_id = t2.prod_id
    group by t1.prod_id, t1.prod_name
) as res
order by res.prod_name

感谢大佬指正，上述想法主要想要表达的是：如果仅仅以prod_name来group by，遇到相同的prod_会name出现问题（因为id才是主键）

另：可以不讲group by 的对象select出来

select t1.prod_name, count(t2.order_num) as total
from Products as t1
left join 
OrderItems as t2
on t1.prod_id = t2.prod_id
group by t1.prod_id, t1.prod_name
order by t1.prod_name

法二

可以用ifnull也可以解决这个问题（当然还是不如大佬提出来的解法优雅）

IFNULL(expression, alt_value)

如果第一个参数的表达式 expression 为 NULL，则返回第二个参数的备用值。

select t1.prod_name, ifnull(res.total, 0) as orders
from Products as t1
left join (
    select t2.prod_id, count(t2.order_num) as total
    from OrderItems as t2
    group by t2.prod_id
) as res
on res.prod_id = t1.prod_id
order by t1.prod_name