【Oracle&SQLServer】并集、交际、补集

1、并集（UNION/UNION ALL）

Oracle&SQLServer中用法一致

UNION去重UNION ALL不去重

 

-- 去重select * from tableaunionselect * from tableb-- 不去重select * from tableaunion allselect * from tableb

 

 2、交集（INTERSECT/EXISTS）

Oracle&SQLServer中用法一致

INTERSECT去重EXISTS不去重

 

-- 去重select * from tableaintersectselect * from tableb-- 不去重select * from tablea awhere 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 tableaminusselect * from tableb-- SQLServer去重select * from tableaexceptselect * from tableb-- Oracle&SQLServer不去重select * from tablea awhere not exists (select 1 from tableb b where a.ID=b.ID)