在知识库中专注阅读，并随时返回相关工具与课程
原文地址：https://oracle-base.com/articles/21c/sql-set-operator-enhancements-21chttps://oracle-base.com/articles/21c/sql-set-operator-enhancements-21c 原文作者：Tim Hall Oracle 21C 新增许多对 SQL 集合运算符的增强，包括 EXCEPT、
原文地址：https://oracle-base.com/articles/21c/sql-set-operator-enhancements-21c 原文作者：Tim Hall
Oracle 21C 新增许多对 SQL 集合运算符的增强，包括 EXCEPT、EXCEPT ALL、MINUS ALL 和 INTERSECT ALL。
在以前的版本中，我们将 ALL 关键字添加到 UNION 以防止删除重复值，从而提高性能。在 Oracle 21C 中，ALL 关键字也可以添加到 MINUS 和 INTERSECT 运算符，因此它们的操作是基于相同行的，而不是基于不同行的。 Oracle 21C 还引入了 EXCEPT 和 EXCEPT ALL 运算符，它们在功能上分别等同于 MINUS 和 MINUS ALL。
本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟
本文中的示例需要创建以下表和数据：
1--drop table departments purge;23create table departments (4 department_id number(2) constraint departments_pk primary key,5 department_name varchar2(14),6 location varchar2(13)7);89insert into departments values (10,'ACCOUNTING','NEW YORK');10insert into departments values (20,'RESEARCH','DALLAS');11insert into departments values (30,'SALES','CHICAGO');12insert into departments values (40,'OPERATIONS','BOSTON');13commit;此表基于 SCOTT 用户中的 DEPT 表，修改为与 SQL for Beginners 系列中使用的表一致。
MINUS 集合运算符返回第一个查询的结果，但是不包含第二个查询结果的所有行。这在功能上等同于 ANSI 集合运算符 EXCEPT DISTINCT，MINUS ALL 集合运算符不会删除重复的行。
首先我们需要创建一些重复的行，通过以下查询，我们使用包含 UNION ALL 的 WITH 子句来复制部门表中的行，然后我们查询该重复数据：
1with d1 as (2 select department_id, department_name3 from departments4 union all5 select department_id, department_name6 from departments7)8select department_id, department_name9from d110where department_id <= 3011order by 1;1213DEPARTMENT_ID DEPARTMENT_NAM14------------- --------------15 10 ACCOUNTING16 10 ACCOUNTING17 20 RESEARCH18 20 RESEARCH19 30 SALES20 30 SALES2122SQL>通过以下查询，我们使用 MINUS 运算符后，由于 MINUS 自带去重，所以实际上第一个查询出的结果去重后只剩下 10，20，30，将第二个查询结果中的 20，30 都减掉后，最后结果集只显示一个 10：
1with d1 as (2 select department_id, department_name3 from departments4 union all5 select department_id, department_name6 from departments7)8select department_id, department_name9from d110where department_id <= 3011minus12select department_id, department_name13from departments14where department_id >= 2015order by 1;1617DEPARTMENT_ID DEPARTMENT_NAM18------------- --------------19 10 ACCOUNTING2021SQL>如果我们将 MINUS 替换成 MINUS ALL 后，由于 MINUS ALL 不会自动去重，因此第一个查询结果为 10，10，20，20，30，30，此时减去第二个查询结果后，剩下的就是 10，10，20，30：
1with d1 as (2 select department_id, department_name3 from departments4 union all5 select department_id, department_name6 from departments7)8select department_id, department_name9from d110where department_id <= 3011minus all12select department_id, department_name13from departments14where department_id >= 2015order by 1;1617DEPARTMENT_ID DEPARTMENT_NAM18------------- --------------19 10 ACCOUNTING20 10 ACCOUNTING21 20 RESEARCH22 30 SALES2324SQL>接下来我们对两个查询都使用 WITH 子句，这样我们在 MINUS ALL 操作的两侧都有重复项，去掉第二个查询的结果 20，20，30，30，最后剩下 2 个 10：
1with d1 as (2 select department_id, department_name3 from departments4 union all5 select department_id, department_name6 from departments7)8select department_id, department_name9from d110where department_id <= 3011minus all12select department_id, department_name13from d114where department_id >= 2015order by 1;1617DEPARTMENT_ID DEPARTMENT_NAM18------------- --------------19 10 ACCOUNTING20 10 ACCOUNTING2122SQL>INTERSECT 集合运算符返回两个查询选择的所有不同行，这意味着只有两个查询共有的那些行才会出现在最终结果集中，INTERSECT ALL 集合运算符不会删除重复的行。
同样的，首先我们需要创建一些重复的行，通过以下查询，我们使用包含 UNION ALL 的 WITH 子句来复制部门表中的行，然后我们查询该重复数据：
1with d1 as (2 select department_id, department_name3 from departments4 union all5 select department_id, department_name6 from departments7)8select department_id, department_name9from d110where department_id <= 3011order by 1;1213DEPARTMENT_ID DEPARTMENT_NAM14------------- --------------15 10 ACCOUNTING16 10 ACCOUNTING17 20 RESEARCH18 20 RESEARCH19 30 SALES20 30 SALES2122SQL>下面的示例使用 INTERSECT 运算符，同样的 INTERSECT 也是自带去重，所以最后查询结果为 20，30：
1with d1 as (2 select department_id, department_name3 from departments4 union all5 select department_id, department_name6 from departments7)8select department_id, department_name9from d110where department_id <= 3011intersect12select department_id, department_name13from departments14where department_id >= 2015order by 1;1617DEPARTMENT_ID DEPARTMENT_NAM18------------- --------------19 20 RESEARCH20 30 SALES2122SQL>我们将 INTERSECT 替换为 INTERSECT ALL 后得到了相同的结果，因为 INTERSECT ALL 之后的查询仅包含部门 20 和 30 的单个副本，因此每个部门只有一个相交：
1with d1 as (2 select department_id, department_name3 from departments4 union all5 select department_id, department_name6 from departments7)8select department_id, department_name9from d110where department_id <= 3011intersect all12select department_id, department_name13from departments14where department_id >= 2015order by 1;1617DEPARTMENT_ID DEPARTMENT_NAM18------------- --------------19 20 RESEARCH20 30 SALES2122SQL>接下来我们对两个查询都使用 WITH 子句，由于两个查询均包含 20，20，30，30，使用 INTERSECT ALL 并不会删除重复行，因此最后结果为 20，20，30，30：
1with d1 as (2 select department_id, department_name3 from departments4 union all5 select department_id, department_name6 from departments7)8select department_id, department_name9from d110where department_id <= 3011intersect all12select department_id, department_name13from d114where department_id >= 2015order by 1;1617DEPARTMENT_ID DEPARTMENT_NAM18------------- --------------19 20 RESEARCH20 20 RESEARCH21 30 SALES22 30 SALES2324SQL>此时，如果我们替换回 INTERSECT，重复项将再次被删除，结果变为 20，30：
1with d1 as (2 select department_id, department_name3 from departments4 union all5 select department_id, department_name6 from departments7)8select department_id, department_name9from d110where department_id <= 3011intersect12select department_id, department_name13from d114where department_id >= 2015order by 1;1617DEPARTMENT_ID DEPARTMENT_NAM18------------- --------------19 20 RESEARCH20 30 SALES2122SQL>EXCEPT 集合运算符返回第一个查询结果并且减去第二个查询结果的所有行。这在功能上等同于 ANSI 集合运算符 EXCEPT DISTINCT 和 MINUS 运算符。
在下面的示例中，第一个查询将返回部门 10、20、30，但第二个查询返回 20，30，EXPECT 去掉第二个查询结果，最后结果返回 10：
1select department_id, department_name2from departments3where department_id <= 304except5select department_id, department_name6from departments7where department_id >= 208order by 1;910DEPARTMENT_ID DEPARTMENT_NAM11------------- --------------12 10 ACCOUNTING13141 row selected.1516SQL>EXCEPT ALL 集合运算符返回第一个查询而不是第二个查询选择的所有行，在功能上等同于 MINUS ALL 运算符。
首先我们需要创建一些重复的行，在以下查询中，我们使用包含 UNION ALL 的 WITH 子句来复制部门表中的行，然后我们查询该重复数据：
1with d1 as (2 select department_id, department_name3 from departments4 union all5 select department_id, department_name6 from departments7)8select department_id, department_name9from d110where department_id <= 3011order by 1;1213DEPARTMENT_ID DEPARTMENT_NAM14------------- --------------15 10 ACCOUNTING16 10 ACCOUNTING17 20 RESEARCH18 20 RESEARCH19 30 SALES20 30 SALES2122SQL>在以下查询中，我们使用了 EXCEPT 运算符，第一个查询结果返回 10，10，20，20，30，30，第二个结果返回 20，30，去掉第二个查询结果后，由于 EXCEPT 等同于 MINUS 会自动去重，因此最后返回 10：
1with d1 as (2 select department_id, department_name3 from departments4 union all5 select department_id, department_name6 from departments7)8select department_id, department_name9from d110where department_id <= 3011expect12select department_id, department_name13from departments14where department_id >= 2015order by 1;1617DEPARTMENT_ID DEPARTMENT_NAM18------------- --------------19 10 ACCOUNTING2021SQL>如果我们将 EXCEPT 切换为 EXCEPT ALL，将不会自动去重，因此最后返回结果为：10，10，20，30：
1with d1 as (2 select department_id, department_name3 from departments4 union all5 select department_id, department_name6 from departments7)8select department_id, department_name9from d110where department_id <= 3011expect all12select department_id, department_name13from departments14where department_id >= 2015order by 1;1617DEPARTMENT_ID DEPARTMENT_NAM18------------- --------------19 10 ACCOUNTING20 10 ACCOUNTING21 20 RESEARCH22 30 SALES2324SQL>最后我们对两个查询都使用 WITH 子句，由于我们在 EXCEPT ALL 操作的两侧都有重复项，现在我们只看到部门 10 的副本，因为部门 20 和 30 的副本都被删除了：
1with d1 as (2 select department_id, department_name3 from departments4 union all5 select department_id, department_name6 from departments7)8select department_id, department_name9from d110where department_id <= 3011except all12select department_id, department_name13from d114where department_id >= 2015order by 1;1617DEPARTMENT_ID DEPARTMENT_NAM18------------- --------------19 10 ACCOUNTING20 10 ACCOUNTING2122SQL>原文最后 expect all 那一段，由于 Tim Hall 的笔误，最后将 expect 写成了 minus，我这里翻译时已经更正！