8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | 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 tables.

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;

create table t1 (
  id           number(2) constraint t1_pk primary key,
  record_type  number(2),
  description  varchar2(12)
);

insert into t1 values (1, 10,'TEN');
insert into t1 values (2, 10,'TEN');
insert into t1 values (3, 20,'TWENTY');
insert into t1 values (4, 20,'TWENTY');
insert into t1 values (5, 30,'THIRTY');
insert into t1 values (6, 30,'THIRTY');
commit;

create table t2 (
  id           number(2) constraint t2_pk primary key,
  record_type  number(2),
  description  varchar2(12)
);

insert into t2 values (1, 20,'TWENTY');
insert into t2 values (2, 30,'THIRTY');
commit;

create table t3 (
  id           number(2) constraint t3_pk primary key,
  record_type  number(2),
  description  varchar2(12)
);

insert into t3 values (1, 20,'TWENTY');
insert into t3 values (2, 20,'TWENTY');
insert into t3 values (3, 30,'THIRTY');
insert into t3 values (4, 30,'THIRTY');
commit;

The T1 table has two copies of record types 10, 20 and 30. The T2 table as one copy of record types 20 and 30. The T3 table has two copies of record types 20 and 30.

select record_type, description from t1;

RECORD_TYPE DESCRIPTION
----------- ------------
         10 TEN
         10 TEN
         20 TWENTY
         20 TWENTY
         30 THIRTY
         30 THIRTY

SQL>


select record_type, description from t2;

RECORD_TYPE DESCRIPTION
----------- ------------
         20 TWENTY
         30 THIRTY

SQL>


select record_type, description from t3;

RECORD_TYPE DESCRIPTION
----------- ------------
         20 TWENTY
         20 TWENTY
         30 THIRTY
         30 THIRTY

SQL>

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.

In the following query we select all rows from T1, minus those from T2. Since the initial set now has duplicate rows removed, we only see a single copy of record type 10, and record types 20 and 30 have been removed entirely.

select record_type, description
from   t1
minus
select record_type, description
from   t2
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         10 TEN

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 record type 10, and the MINUS ALL has only removed one copy of record types 20 and 30 from the result set.

select record_type, description
from   t1
minus all
select record_type, description
from   t2
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         10 TEN
         10 TEN
         20 TWENTY
         30 THIRTY

SQL>

This time we select all the rows from T1, minus the rows from T3. Now we only see the duplicates of record type 10, since both copies of record types 20 and 30 are removed.

select record_type, description
from   t1
minus all
select record_type, description
from   t3
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         10 TEN
         10 TEN

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.

In the following query we use INTERSECT to select all rows from T1 that are present in T2. Notice there is one row each for record types 20 and 30, as both these appear in the result sets for their respective queries.

select record_type, description
from   t1
intersect
select record_type, description
from   t2
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         20 TWENTY
         30 THIRTY

SQL>

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

select record_type, description
from   t1
intersect all
select record_type, description
from   t2
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         20 TWENTY
         30 THIRTY

SQL>

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

select record_type, description
from   t1
intersect all
select record_type, description
from   t3
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         20 TWENTY
         20 TWENTY
         30 THIRTY
         30 THIRTY

SQL>

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

select record_type, description
from   t1
intersect
select record_type, description
from   t3
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         20 TWENTY
         30 THIRTY

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 select would return record types 10, 20, 30, but record types 20 and 30 are removed because they are returned by the second select. This leaves a single rows for record type 10.

select record_type, description
from   t1
except
select record_type, description
from   t2
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         10 TEN

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.

In the following query we use EXCEPT to select all rows from T1 that are not present in T2. Since the initial set now has duplicate rows removed, we only see a single copy of record type 10, and record types 20 and 30 have been removed entirely.

select record_type, description
from   t1
except
select record_type, description
from   t2
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         10 TEN

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 record type 10, and the EXCEPT ALL has only removed one copy of record types 20 and 30 from the result set.

select record_type, description
from   t1
except all
select record_type, description
from   t2
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         10 TEN
         10 TEN
         20 TWENTY
         30 THIRTY

SQL>

This time we select all the rows from T1, except the rows from T3. Now we only see the duplicates of record type 10, since both copies of record types 20 and 30 are removed.

select record_type, description
from   t1
except all
select record_type, description
from   t3
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         10 TEN
         10 TEN

SQL>

Explicit DISTINCT

In previous releases the DISTINCT keyword could not be added explicitly to the UNION, MINUS and INTERSECT set operators. It's presence was implied. In Oracle 21c the DISTINCT keyword can be added explicitly to these and the new EXCEPT set operator.

select record_type, description
from   t1
union distinct
select record_type, description
from   t2
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         10 TEN
         20 TWENTY
         30 THIRTY
SQL>


select record_type, description
from   t1
minus distinct
select record_type, description
from   t2
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         10 TEN
SQL>


select record_type, description
from   t1
intersect distinct
select record_type, description
from   t2
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         20 TWENTY
         30 THIRTY
SQL>


select record_type, description
from   t1
except distinct
select record_type, description
from   t2
order by 1;

RECORD_TYPE DESCRIPTION
----------- ------------
         10 TEN
SQL>

Query Transformations

The EXCEPT and EXCEPT ALL set operators are query transformations. When we issue them, they are transformed to MINUS and MINUS ALL respectively.

In all cases, the addition of the DISTINCT keyword with the set operator is transformed to the equivalent statement without the DISTINCT keyword.

Here are the transformations, visible in a 10053 trace file.

EXCEPT             -> MINUS
EXCEPT ALL         -> MINUS ALL
EXCEPT DISTINCT    -> MINUS
UNION DISTINCT     -> UNION
MINUS DISTINCT     -> MINUS
INTERSECT DISTINCT -> INTERSECT

For more information see:

Hope this helps. Regards Tim...

Back to the Top.