题解 | #确定哪些订单购买了 prod_id 为 BR01 的产品（一）#
确定哪些订单购买了 prod_id 为 BR01 的产品（一）
http://www.nowcoder.com/practice/b692b0174c0444fa9452aee2d082fbbb
自己写的几种加上各位牛友写的一些整理了一下, 供大家参考:
- where版本
select cust_id, order_date
from Orders o, OrderItems oi
where prod_id = 'BR01' and o.order_num = oi.order_num
order by order_date
- 子查询版本
select cust_id, order_date from Orders
where order_num in (
select order_num from OrderItems
where prod_id = 'BR01'
)
order by order_date;
- 左连接版本
select
cust_id,
order_date
from
Orders o
LEFT JOIN OrderItems oi ON o.order_num = oi.order_num
where
prod_id = 'BR01'
order by
order_date;
- 自然连接版本
select
cust_id,
order_date
from
Orders
NATURAL JOIN OrderItems
where prod_id = 'BR01'
order by order_date;
- 内连接 类似where
select
cust_id,
order_date
from
Orders o
inner JOIN OrderItems oi
on o.order_num = oi.order_num and prod_id = 'BR01'
order by order_date;
- join using 类似自然连接
select
cust_id,
order_date
from
Orders
# 相当于自然连接对相同的列进行连接
join OrderItems using(order_num)
where
prod_id = 'BR01'
order by
order_date;
