1、差集
????假設(shè)表class_a和class_b的內(nèi)容分別如下：
7.PNG
8.PNG
????需求：求A-B
????法一：left join+null
select a.* from class_a a
left join
class_b b
on a.id=b.id
where b.id is null
????法二：not exists或者not in
select a.* from class_a a
where not EXISTS(select b.id from class_b b where a.id=b.id)
-- 或者
select a.* from class_a a
where a.id not in (select b.id from class_b b)
????注：not in當(dāng)子查詢存在null時(shí)，會(huì)返回null
????A-B結(jié)果為
9.PNG
2管毙、交集
select a.* from class_a a
where a.id in (select b.id from class_b b);
-- 或者
select a.* from class_a a
where EXISTS(select b.id from class_b b where a.id=b.id);
-- 或者
select a.* from class_a a
inner join
class_b b
on a.id=b.id;
????結(jié)果
10.PNG
3、并集
select a.*,b.name from class_a a
left join
class_b b
on a.id=b.id
union
select b.id,a.name,b.name from class_a a
right join
class_b b
on a.id=b.id;
select b.* from class_b b
on a.id=b.id;
????結(jié)果
12.PNG
????注：MySQL不支持full join
4、異或集
select a.id,a.name name_a,b.name name_b from class_a a
left join
class_b b
on a.id=b.id
where b.id is null
union
select b.id,a.name name_a,b.name name_b from class_a a
right join
class_b b
on a.id=b.id
where a.id is null;
-- 或者
select *
from
(select a.id, a.name name_a,b.name name_b from class_a a
left join
class_b b
on a.id=b.id
union
select b.id,a.name name_a,b.name name_b from class_a a
right join
class_b b
on a.id=b.id
) t
where t.name_a is null or t.name_b is null;
????結(jié)果
13.PNG
5.關(guān)系除法運(yùn)算
????關(guān)系除法概念：
????https://blog.csdn.net/weixin_42023723/article/details/80876568
????假設(shè)表items和shopitems的內(nèi)容分別如下：
14.PNG
15.PNG
????需求：求shopitems÷items舀武，即擁有items表中所有商品的商店
select s1.shop from shopitems s1
where s1.item in (select item from items)
GROUP BY s1.shop
having count(distinct s1.item)=(select count(*) from items)
????結(jié)果
image.png
以上內(nèi)容除了最后一個(gè)關(guān)系除法砾跃，都來自《SQL進(jìn)階教程》
