侧边栏壁纸
  • 累计撰写 138 篇文章
  • 累计创建 13 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

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

DarkAthena
2021-11-19 / 0 评论 / 0 点赞 / 886 阅读 / 4089 字

前言

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交集
0
  1. 支付宝打赏

    qrcode alipay
  2. 微信打赏

    qrcode weixin
博主关闭了所有页面的评论