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.
◉ 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
Posts
0 comments:
Post a Comment