[译] Oracle Database 21c 中的 SQL 集合运算符增强功能（EXCEPT、EXCEPT ALL、MINUS ALL、INTERSECT ALL）

原文地址：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。

目录

• 环境准备
• MINUS ALL
• INTERSECT ALL
• EXCEPT
• EXCEPT 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;

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13

此表基于 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>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21
* 22

通过以下查询，我们使用 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>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21

如果我们将 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>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21
* 22
* 23
* 24

接下来我们对两个查询都使用 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>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21
* 22

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>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21
* 22

下面的示例使用 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>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21
* 22

我们将 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>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21
* 22

接下来我们对两个查询都使用 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>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21
* 22
* 23
* 24

此时，如果我们替换回 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>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21
* 22

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>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16

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>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21
* 22

在以下查询中，我们使用了 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>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21

如果我们将 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>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21
* 22
* 23
* 24

最后我们对两个查询都使用 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>

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21
* 22

原文最后 expect all 那一段，由于 Tim Hall 的笔误，最后将 expect 写成了 minus，我这里
翻译
时已经更正！