inner join & outer join 专题

基于原理理解，介绍 inner join 和 outer join 的使用技巧，这里不涉及外连接行列转换。

1. inner join：

在店铺表中找到含商品表中所有商品的店铺：

select shop
from items,shopitems
where items.item = shopitems.item
group by shop
having count(*) = (select count(*) from items)

---针对集合设定条件：inner join 过滤出品类相同的记录；having 条件为聚合后两者品类数一致。

2. outer join：

在店铺表中找到仅含商品表中所有商品的店铺：

select shop
from shopitems left join items
on shopitems.item = items.item
group by shop
having count(items.item) = (select count(*) from items)

---对 left join 匹配到的右列品类计数；having 条件为聚合后两者品类数一致。

差集 A-B：

select class_a.id,class_a.name
from class_a left join class_b
on class_a.id = class_b.id
where class_b.name is null

---left join 后匹配到的为共有部分；is null 为非共有。

力扣1045：

select distinct customer_id 
from Customer,Product 
where Customer.product_key = Product.product_key 
group by customer_id 
having count(distinct Customer.product_key)=
    (select count(distinct Product.product_key)
    from Product)

 --指向性明显，先 join 后 count。

-END