DBA Hub

📋Steps in this guide1/7

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.

oracle 21cconfigurationintermediate
by OracleDba
15 views
1

Setup

The examples in this article require the following tables. The table has two copies of record types 10, 20 and 30. The table as one copy of record types 20 and 30. The table has two copies of record types 20 and 30.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
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;

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

MINUS ALL

The set operator returns all distinct rows selected by the first query but not the second. This is functionally equivalent to the ANSI set operator . The set operator doesn't remove duplicate rows. In the following query we select all rows from , minus those from . 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. If we switch the to a , the duplicates are no longer removed from the initial set, so we see two copies of record type 10, and the has only removed one copy of record types 20 and 30 from the result set. This time we select all the rows from , minus the rows from . Now we only see the duplicates of record type 10, since both copies of record types 20 and 30 are removed.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
select record_type, description
from   t1
minus
select record_type, description
from   t2
order by 1;

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

SQL>

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>

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

INTERSECT ALL

The 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 set operator doesn't remove duplicate rows. In the following query we use to select all rows from that are present in . Notice there is one row each for record types 20 and 30, as both these appear in the result sets for their respective queries. We repeat with and get the same result, as the query after the only contains a single copy of record types 20 and 30, so there is only a single intersect for each. This time we use for the second query, so we have duplicates on both sides of the operation. We now see duplicates due to multiple matches on each side. If we switch back to , the duplicates are removed again.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
select record_type, description
from   t1
intersect
select record_type, description
from   t2
order by 1;

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

SQL>

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>

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>

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

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

SQL>
4

EXCEPT

The set operator returns all distinct rows selected by the first query but not the second. This is functionally equivalent to the ANSI set operator and the 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
select record_type, description
from   t1
except
select record_type, description
from   t2
order by 1;

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

SQL>
5

EXCEPT ALL

The set operator returns all rows selected by the first query but not the second. This is functionally equivalent to the operator. In the following query we use to select all rows from that are not present in . 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. If we switch the to an , the duplicates are no longer removed from the initial set, so we see two copies of record type 10, and the has only removed one copy of record types 20 and 30 from the result set. This time we select all the rows from , except the rows from . Now we only see the duplicates of record type 10, since both copies of record types 20 and 30 are removed.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
select record_type, description
from   t1
except
select record_type, description
from   t2
order by 1;

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

SQL>

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>

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

Explicit DISTINCT

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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>
7

Query Transformations

The and set operators are query transformations. When we issue them, they are transformed to and respectively. In all cases, the addition of the keyword with the set operator is transformed to the equivalent statement without the keyword. Here are the transformations, visible in a 10053 trace file. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
EXCEPT             -> MINUS
EXCEPT ALL         -> MINUS ALL
EXCEPT DISTINCT    -> MINUS
UNION DISTINCT     -> UNION
MINUS DISTINCT     -> MINUS
INTERSECT DISTINCT -> INTERSECT

Comments (0)

Please to add comments

No comments yet. Be the first to comment!