题解 | #返回产品名称和与之相关的订单号#

##该题要求使用outer join,但是由于mysql不支持outer join,所以只能使用left join +right join实现全联结##

1. left join

• select prod_name,order_num
• from Products A
• left join OrderItems B
• on A.prod_id=B.prod_id

1. right join

• 由于left join已经包含相交部分，所以right join 需要剔除相交部分
• select prod_name,order_num
• from Products C
• right join OrderItems D
• on C.prod_id=D.prod_id
• where C.prod_id is null

1. 然后是用union联结起来（自动去重），再排序

• select prod_name,order_num
• from Products A
• left join OrderItems B
• on A.prod_id=B.prod_id
• union
• select prod_name,order_num
• from Products C
• right join OrderItems D
• on C.prod_id=D.prod_id
• where C.prod_id is null
• order by cust_name