前言
SQL集运算符,比较常见的有"UNION" 、"UNION ALL"、"MINUS",而ORACLE在21c版本新增了多个SQL集运算符,至此已支持ANSI SQL中定义的所有SQL集运算关键字,方便各类数据库移植。
EXCEPT/EXCEPT ALL
EXCEPT意思为排除,其实和MINUS是等效的,即对于上方数据集的元素,只要下方数据集中存在,就剔除,只返回下方数据集中不存在的元素,但需要注意的是,如果上方数据集中存在重复元素,它会去重只返回其一。
with t1 as
(select COLUMN_VALUE C1
from table(ora_mining_varchar2_nt('A', 'B', 'B', 'C', 'C', 'D', 'Z'))),
T2 AS
(select COLUMN_VALUE C1
from table(ora_mining_varchar2_nt('A', 'B', 'B', 'C', 'E', 'F', 'F')))
SELECT C1 FROM T1
EXCEPT
SELECT C1 FROM T2;
C1
---
D
Z
with t1 as
(select COLUMN_VALUE C1
from table(ora_mining_varchar2_nt('A', 'B', 'B', 'C', 'C', 'D', 'Z'))),
T2 AS
(select COLUMN_VALUE C1
from table(ora_mining_varchar2_nt('A', 'B', 'B', 'C', 'E', 'F', 'F')))
SELECT C1 FROM T2
EXCEPT
SELECT C1 FROM T1;
C1
---
E
F
EXCEPT ALL和EXCEPT类似,区别只在于,如果上方数据集中存在相同元素,它是逐个剔除,重复元素分开算。
with t1 as
(select COLUMN_VALUE C1
from table(ora_mining_varchar2_nt('A', 'B', 'B', 'C', 'C', 'D', 'Z'))),
T2 AS
(select COLUMN_VALUE C1
from table(ora_mining_varchar2_nt('A', 'B', 'B', 'C', 'E', 'F', 'F')))
SELECT C1 FROM T1
EXCEPT ALL
SELECT C1 FROM T2;
C1
---
C
D
Z
--注意:虽然上下都有C,但还是返回了一个C
with t1 as
(select COLUMN_VALUE C1
from table(ora_mining_varchar2_nt('A', 'B', 'B', 'C', 'C', 'D', 'Z'))),
T2 AS
(select COLUMN_VALUE C1
from table(ora_mining_varchar2_nt('A', 'B', 'B', 'C', 'E', 'F', 'F')))
SELECT C1 FROM T2
EXCEPT ALL
SELECT C1 FROM T1;
C1
---
E
F
F
INTERSECT/INTERSECT ALL
INTERSECT是交集,即返回上下两个数据集中相同的部分,对于数据集中相同的元素,去重只返回其一。
with t1 as
(select COLUMN_VALUE C1
from table(ora_mining_varchar2_nt('A', 'B', 'B', 'C', 'C', 'D', 'Z'))),
T2 AS
(select COLUMN_VALUE C1
from table(ora_mining_varchar2_nt('A', 'B', 'B', 'C', 'E', 'F', 'F')))
SELECT C1 FROM T1
INTERSECT
SELECT C1 FROM T2;
C1
---
A
B
C
INTERSECT ALL和INTERSECT类似,区别只在于,如果数据集中存在相同元素,它返回的数据不会去重。
with t1 as
(select COLUMN_VALUE C1
from table(ora_mining_varchar2_nt('A', 'B', 'B', 'C', 'C', 'D', 'Z'))),
T2 AS
(select COLUMN_VALUE C1
from table(ora_mining_varchar2_nt('A', 'B', 'B', 'C', 'E', 'F', 'F')))
SELECT C1 FROM T1
INTERSECT ALL
SELECT C1 FROM T2;
C1
---
A
B
B
C
总结
其实可以发现,新增的这些用法,和之前的UNION/UNION ALL具有相同的特征,归纳整理如下
运算符 | 含义 | 结果是否去重 | 备注 |
---|---|---|---|
UNION | 并集 | 是 | |
UNION ALL | 并集 | 否 | |
MINUS | 差集 | 是 | |
MINUS ALL | 差集 | 否 | |
EXCEPT | 差集 | 是 | 等效于MINUS |
EXCEPT ALL | 差集 | 否 | 等效于MINUS ALL |
INTERSECT | 交集 | 是 | |
INTERSECT ALL | 交集 | 否 |