题解 | #确定哪些订单购买了 prod_id 为 BR01 的产品（一）#

自己写的几种加上各位牛友写的一些整理了一下, 供大家参考:

1. 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

2. 子查询版本

  select cust_id, order_date from Orders
  where order_num in (
      select order_num from OrderItems
      where prod_id = 'BR01'
  )
  order by order_date;

3. 左连接版本

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;

4. 自然连接版本

select
  cust_id,
  order_date
from
  Orders
  NATURAL JOIN OrderItems
where prod_id = 'BR01'
order by  order_date;

5. 内连接 类似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;

6. join using 类似自然连接

select
 cust_id,
 order_date
from
 Orders
 # 相当于自然连接对相同的列进行连接
 join OrderItems using(order_num)
where
 prod_id = 'BR01'
order by
 order_date;