8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- MINUS ALL Set Operator in Oracle Database 21c
- INTERSECT ALL Set Operator in Oracle Database 21c
- EXCEPT and EXCEPT ALL Set Operators in Oracle 21c
- SQL for Beginners (Part 6) : Set Operators
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:
- Set Operators
- MINUS ALL Set Operator in Oracle Database 21c
- INTERSECT ALL Set Operator in Oracle Database 21c
- EXCEPT and EXCEPT ALL Set Operators in Oracle 21c
- SQL for Beginners (Part 6) : Set Operators
Hope this helps. Regards Tim...