【ORACLE】21c版本新特性之增强的sql集运算符"MINUS ALL"、"EXCEPT ALL"、"INTERSECT ALL"-DA-技术分享

前言

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	交集	否