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)