8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 21c » Here

SQL Set Operator Enhancements (EXCEPT, EXCEPT ALL, MINUS ALL, INTERSECT ALL) in Oracle Database 21c

Oracle 21c includes a number of enhancements to SQL set operators including EXCEPT, EXCEPT ALL, MINUS ALL and INTERSECT ALL.

In previous releases the ALL keyword was added to UNION to prevent the removal of duplicate values, and thereby improve performance. In Oracle 21c the ALL keyword can also be added to the MINUS and INTERSECT operators, so their operations are on a per-row basis, rather than a distinct-row basis. Oracle 21c also introduces EXCEPT and EXCEPT ALL operators, which are functionally equivalent to MINUS and MINUS ALL respectively.

Related articles.

Setup

The examples in this article require the following table.

--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;

This table is a variant of the DEPT tables from the SCOTT schema. It is modified to be in line with the table used in the SQL for Beginners series.

MINUS ALL

The MINUS set operator returns all distinct rows selected by the first query but not the second. This is functionally equivalent to the ANSI set operator EXCEPT DISTINCT. The MINUS ALL set operator doesn't remove duplicate rows.

First we need to create some duplicate rows. In the following query we use a WITH clause containing a UNION ALL to duplicate the rows in the departments table. We then query that duplicate data.

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>

In the following query we add a MINUS operator to remove departments 20 and 30 from the result set. Since the initial set now has duplicate rows removed, we only see a single copy of department 10, and departments 20 and 30 have been removed entirely.

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>

If we switch the MINUS to a MINUS ALL, the duplicates are no longer removed from the initial set, so we see two copies of department 10, and the MINUS ALL has only removed one copy of department 20 and 30 from the result set.

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>

This time we use the WITH clause for both queries, so we have duplicates on both sides of the MINUS ALL operation. Now we only see the duplicates of department 10, since both copies of departments 20 and 30 are removed.

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

The INTERSECT set operator returns all distinct rows selected by both queries. That means only those rows common to both queries will be present in the final result set. The INTERSECT ALL set operator doesn't remove duplicate rows.

First we need to create some duplicate rows. In the following query we use a WITH clause containing a UNION ALL to duplicate the rows in the departments table. We then query that duplicate data.

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>

The example below uses the INTERSECT operator. Notice there is one row each for departments 20 and 30, as both these appear in the result sets for their respective queries.

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>

We repeat with INTERSECT ALL and get the same result, as the query after the INTERSECT ALL only contains a single copy of departments 20 and 30, so there is only a single intersect for each.

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>

This time we use the WITH clause for both queries, so we have duplicates on both sides of the INTERSECT ALL operation. We now see duplicates due to multiple matches on each side.

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>

If we switch back to INTERSECT, the duplicates are removed again.

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

The EXCEPT set operator returns all distinct rows selected by the first query but not the second. This is functionally equivalent to the ANSI set operator EXCEPT DISTINCT and the MINUS operator.

In the example below, the first query would return departments 10, 20, 30, but departments 20 and 30 are removed because they are returned by the second query. This leaves a single rows for department 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

The EXCEPT ALL set operator returns all rows selected by the first query but not the second. This is functionally equivalent to the MINUS ALL operator.

First we need to create some duplicate rows. In the following query we use a WITH clause containing a UNION ALL to duplicate the rows in the departments table. We then query that duplicate data.

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>

In the following query we add an EXCEPT operator to remove departments 20 and 30 from the result set. Since the initial set now has duplicate rows removed, we only see a single copy of department 10, and departments 20 and 30 have been removed entirely.

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>

If we switch the EXCEPT to an EXCEPT ALL, the duplicates are no longer removed from the initial set, so we see two copies of department 10, and the EXCEPT ALL has only removed one copy of department 20 and 30 from the result set.

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>

This time we use the WITH clause for both queries, so we have duplicates on both sides of the EXCEPT ALL operation. Now we only see the duplicates of department 10, since both copies of departments 20 and 30 are removed.

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>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.