sql join和correlated subenquiry的区别

Question

题目要求生成如下表格：

相关表格两张：

正确答案：

我的答案，我没有使用join链接两张表格，而是在select中用where把两张表的product id链接起来，为什么结果不对？

Answer 1

select 
p.product_id,
p.name,
(select count(*) from order_items oi where oi.product_id = p.product_id) as orders,
if ((select count(*) from order_items oi where oi.product_id = p.product_id) > 1, 'Many times', 'Once') as frequency
from products p
group by product_id, name

方式二：

select 
temp.product_id,
temp.name,
temp.orders,
if (tmep.orders>1, 'Many times', 'Once') as frequency
from(
select 
p.product_id,
p.name,
(select count(*) from order_items oi where oi.product_id = p.product_id) as orders
from products p
group by product_id, name
) temp