1、并集（UNION/UNION ALL）
Oracle&SQLServer中用法一致
| UNION | 去重 |
| UNION ALL | 不去重 |
-- 去重
select * from tablea
union
select * from tableb -- 不去重
select * from tablea
union all
select * from tableb
2、交集（INTERSECT/EXISTS）
Oracle&SQLServer中用法一致
| INTERSECT | 去重 |
| EXISTS | 不去重 |
-- 去重
select * from tablea
intersect
select * from tableb -- 不去重
select * from tablea a
where exists (select 1 from tableb b where a.ID=b.ID)
3、补集（MINUS/EXCEPT/NOT EXISTS）
Oracle：
| MINUS | 去重 |
| NOT EXISTS | 不去重 |
SQLServer：
| EXCEPT | 去重 |
| NOT EXISTS | 不去重 |
-- Oracle去重
select * from tablea
minus
select * from tableb -- SQLServer去重
select * from tablea
except
select * from tableb -- Oracle&SQLServer不去重
select * from tablea a
where not exists (select 1 from tableb b where a.ID=b.ID)