Outer Join Between Multiple Tables
在订单表和顾客表连接的基础上
上次学习内容连接
连接订单表和发货人表
让发货人的名字出现在结果中
上一节学到:
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id
再次基础上,增加:
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customers c
LEFT JOIN orders o -- 外连接
ON c.customer_id = o.customer_id
JOIN shippers sh -- 内连接
ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id
现在会出现一个问题,order没有显示全部
因此需要继续借用外连接:
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customers c
LEFT JOIN orders o -- 外连接
ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id
注意 尽可能少使用右连接
练习
返回一个列表,包括order_date、order_id、firts_name、shipper、status
分析:
需要使用到四个表
1-order 表
2-order_status
3-shipper表
4-customers表
USE sql_store;
SELECT
o.order_date,
o.order_id,
c.first_name,
s.name AS shipper,
os.name AS status
FROM orders o
JOIN customers c -- 思考这里为什么不需要用外连接 因为每笔订单当中都有顾客
on c.customer_id = o.customer_id
LEFT JOIN shippers s
on o.shipper_id = s.shipper_id
JOIN order_statuses os -- 反馈已有order的status 故不加外连接
ON o.status = os.order_status_id
答案