作者：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。
目录
环境准备
本文中的示例需要创建以下表和数据：
--drop table departments purge;
create table departments (
department_id number(2) constraint departments_pk primary key,
department_name varchar2(14),
location varchar2(13)
);
insert into departments values (10,'ACCOUNTING','NEW YORK');
insert into departments values (20,'RESEARCH','DALLAS');
insert into departments values (30,'SALES','CHICAGO');
insert into departments values (40,'OPERATIONS','BOSTON');
commit;
此表基于 SCOTT 用户中的 DEPT 表，修改为与 SQL for Beginners 系列中使用的表一致。
MINUS ALL
MINUS 集合运算符返回第一个查询的结果，但是不包含第二个查询结果的所有行。这在功能上等同于 ANSI 集合运算符 EXCEPT DISTINCT，MINUS ALL 集合运算符不会删除重复的行。
首先我们需要创建一些重复的行，通过以下查询，我们使用包含 UNION ALL 的 WITH 子句来复制部门表中的行，然后我们查询该重复数据：
with d1 as (
select department_id, department_name
from departments
union all
select department_id, department_name
from departments
)
select department_id, department_name
from d1
where department_id <= 30
order by 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
10 ACCOUNTING
20 RESEARCH
20 RESEARCH
30 SALES
30 SALES
SQL>
通过以下查询，我们使用 MINUS 运算符后，由于 MINUS 自带去重，所以实际上第一个查询出的结果去重后只剩下 10，20，30，将第二个查询结果中的 20，30 都减掉后，最后结果集只显示一个 10：
with d1 as (
select department_id, department_name
from departments
union all
select department_id, department_name
from departments
)
select department_id, department_name
from d1
where department_id <= 30
minus
select department_id, department_name
from departments
where department_id >= 20
order by 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
SQL>
如果我们将 MINUS 替换成 MINUS ALL 后，由于 MINUS ALL 不会自动去重，因此第一个查询结果为 10，10，20，20，30，30，此时减去第二个查询结果后，剩下的就是 10，10，20，30：
with d1 as (
select department_id, department_name
from departments
union all
select department_id, department_name
from departments
)
select department_id, department_name
from d1
where department_id <= 30
minus all
select department_id, department_name
from departments
where department_id >= 20
order by 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
10 ACCOUNTING
20 RESEARCH
30 SALES
SQL>
接下来我们对两个查询都使用 WITH 子句，这样我们在 MINUS ALL 操作的两侧都有重复项，去掉第二个查询的结果 20，20，30，30，最后剩下 2 个 10：
with d1 as (
select department_id, department_name
from departments
union all
select department_id, department_name
from departments
)
select department_id, department_name
from d1
where department_id <= 30
minus all
select department_id, department_name
from d1
where department_id >= 20
order by 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
10 ACCOUNTING
SQL>
INTERSECT ALL
INTERSECT 集合运算符返回两个查询选择的所有不同行，这意味着只有两个查询共有的那些行才会出现在最终结果集中，INTERSECT ALL 集合运算符不会删除重复的行。
同样的，首先我们需要创建一些重复的行，通过以下查询，我们使用包含 UNION ALL 的 WITH 子句来复制部门表中的行，然后我们查询该重复数据：
with d1 as (
select department_id, department_name
from departments
union all
select department_id, department_name
from departments
)
select department_id, department_name
from d1
where department_id <= 30
order by 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
10 ACCOUNTING
20 RESEARCH
20 RESEARCH
30 SALES
30 SALES
SQL>
下面的示例使用 INTERSECT 运算符，同样的 INTERSECT 也是自带去重，所以最后查询结果为 20，30：
with d1 as (
select department_id, department_name
from departments
union all
select department_id, department_name
from departments
)
select department_id, department_name
from d1
where department_id <= 30
intersect
select department_id, department_name
from departments
where department_id >= 20
order by 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
20 RESEARCH
30 SALES
SQL>
我们将 INTERSECT 替换为 INTERSECT ALL 后得到了相同的结果，因为 INTERSECT ALL 之后的查询仅包含部门 20 和 30 的单个副本，因此每个部门只有一个相交：
with d1 as (
select department_id, department_name
from departments
union all
select department_id, department_name
from departments
)
select department_id, department_name
from d1
where department_id <= 30
intersect all
select department_id, department_name
from departments
where department_id >= 20
order by 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
20 RESEARCH
30 SALES
SQL>
接下来我们对两个查询都使用 WITH 子句，由于两个查询均包含 20，20，30，30，使用 INTERSECT ALL 并不会删除重复行，因此最后结果为 20，20，30，30：
with d1 as (
select department_id, department_name
from departments
union all
select department_id, department_name
from departments
)
select department_id, department_name
from d1
where department_id <= 30
intersect all
select department_id, department_name
from d1
where department_id >= 20
order by 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
20 RESEARCH
20 RESEARCH
30 SALES
30 SALES
SQL>
此时，如果我们替换回 INTERSECT，重复项将再次被删除，结果变为 20，30：
with d1 as (
select department_id, department_name
from departments
union all
select department_id, department_name
from departments
)
select department_id, department_name
from d1
where department_id <= 30
intersect
select department_id, department_name
from d1
where department_id >= 20
order by 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
20 RESEARCH
30 SALES
SQL>
EXCEPT
EXCEPT 集合运算符返回第一个查询结果并且减去第二个查询结果的所有行。这在功能上等同于 ANSI 集合运算符 EXCEPT DISTINCT 和 MINUS 运算符。
在下面的示例中，第一个查询将返回部门 10、20、30，但第二个查询返回 20，30，EXPECT 去掉第二个查询结果，最后结果返回 10：
select department_id, department_name
from departments
where department_id <= 30
except
select department_id, department_name
from departments
where department_id >= 20
order by 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
1 row selected.
SQL>
EXCEPT ALL
EXCEPT ALL 集合运算符返回第一个查询而不是第二个查询选择的所有行，在功能上等同于 MINUS ALL 运算符。
首先我们需要创建一些重复的行，在以下查询中，我们使用包含 UNION ALL 的 WITH 子句来复制部门表中的行，然后我们查询该重复数据：
with d1 as (
select department_id, department_name
from departments
union all
select department_id, department_name
from departments
)
select department_id, department_name
from d1
where department_id <= 30
order by 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
10 ACCOUNTING
20 RESEARCH
20 RESEARCH
30 SALES
30 SALES
SQL>
在以下查询中，我们使用了 EXCEPT 运算符，第一个查询结果返回 10，10，20，20，30，30，第二个结果返回 20，30，去掉第二个查询结果后，由于 EXCEPT 等同于 MINUS 会自动去重，因此最后返回 10：
with d1 as (
select department_id, department_name
from departments
union all
select department_id, department_name
from departments
)
select department_id, department_name
from d1
where department_id <= 30
expect
select department_id, department_name
from departments
where department_id >= 20
order by 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
SQL>
如果我们将 EXCEPT 切换为 EXCEPT ALL，将不会自动去重，因此最后返回结果为：10，10，20，30：
with d1 as (
select department_id, department_name
from departments
union all
select department_id, department_name
from departments
)
select department_id, department_name
from d1
where department_id <= 30
expect all
select department_id, department_name
from departments
where department_id >= 20
order by 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
10 ACCOUNTING
20 RESEARCH
30 SALES
SQL>
最后我们对两个查询都使用 WITH 子句，由于我们在 EXCEPT ALL 操作的两侧都有重复项，现在我们只看到部门 10 的副本，因为部门 20 和 30 的副本都被删除了：
with d1 as (
select department_id, department_name
from departments
union all
select department_id, department_name
from departments
)
select department_id, department_name
from d1
where department_id <= 30
except all
select department_id, department_name
from d1
where department_id >= 20
order by 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
10 ACCOUNTING
SQL>
原文最后 expect all 那一段，由于 Tim Hall 的笔误，最后将 expect 写成了 minus，我这里翻译时已经更正！
