子查询和内连接

-- 当子查询需要连接外表作筛选条件时，显得很晦涩
select
    p.prod_name,
    (
        select sum(quantity) from OrderItems
        where prod_id in (
            select prod_id from OrderItems
            where prod_id = p.prod_id -- 相当于借助外表进行分组
        )
    ) as quant_sold
from Products p;

-- 正儿八经的连接，无论隐式还是join显式，一看就明了
select
    p.prod_name,
    sum(oi.quantity) as quant_sold
from Products p,OrderItems oi
where p.prod_id = oi.prod_id
group by p.prod_name;