DB2对集合的处理
有a和b两个集合
数据:
a数据
b数据
一、交集
![](https://img-blog.csdnimg.cn/direct/97fde583344542f3973ac11501ba310b.png)
INTERSECT
INTERSECT ALL(等效INTERSECT)
select name, age from sys_user_a
intersect
select name, age from sys_user_b
select name, age from sys_user_a
intersect all
select name, age from sys_user_b
二、差集
![](https://img-blog.csdnimg.cn/direct/e5f795edfb25418cafd6fb140f0fcd18.png)
EXCEPT
EXCEPT ALL(等效EXCEPT)
select name, age from sys_user_a
except
select name, age from sys_user_b
![](https://img-blog.csdnimg.cn/direct/eeafb893fb2744998d31d76f21c2cba6.png)
select name, age from sys_user_a
except all
select name, age from sys_user_b
![](https://img-blog.csdnimg.cn/direct/da1bb2b8e3014b67964903463bf63774.png)
三、合集
![](https://img-blog.csdnimg.cn/direct/352fe3c76c9e4b789b4423e16820187a.png)
UNION(消重合集)
UNION ALL(不消重合集)
select name, age from sys_user_a
union
select name, age from sys_user_b
![](https://img-blog.csdnimg.cn/direct/bfe052eb1d2d48199b2bb73941af88b0.png)
当使用 union all 时,可以看到出现了两条(‘李莉’, ‘14’)的数据,而在 union 时只有一条
select name, age from sys_user_a
union all
select name, age from sys_user_b
![](https://img-blog.csdnimg.cn/direct/ad2ca25be86046c185a2fc03f8b1d518.png)
四、其他情况
![4-1](https://img-blog.csdnimg.cn/direct/717f5e49d40a426d87a090e09f80e042.png)
图4-1可以使用图4-2和图4-3的合集表示,也可以使用图3-1和图1-1的差集表示
![4-2](https://img-blog.csdnimg.cn/direct/b32be4577b6045b2aaf65d11811405da.png)
![4-3](https://img-blog.csdnimg.cn/direct/c0b603d61cac43039f7b9241eb6b50cd.png)
对于图4-2和图4-3的情况可以使用NOT IN 子查询、LEFT JOIN 和 NULL条件、NOT EXISTS子查询
以图4-2的效果为例
select name, age from sys_user_a
where (name, age) not in (select name, age from sys_user_b)
select a.name, a.age from sys_user_a a
left join (select name, age from sys_user_b) b on a.name = b.name and a.age = b.age
where b.name is null
select name, age from sys_user_a a
where not exists (select name, age from sys_user_b b where a.name = b.name and a.age = b.age)