EXCEPT/MINUS

The EXCEPT operator subtracts the set of rows returned by the right-side operator from the set of rows returned by the left-side operator and removes duplicates.

In Oracle mode, the MINUS operator is generally used to perform set subtraction, and in MySQL mode, the EXCEPT operator is generally used to perform set subtraction. In MySQL mode of OceanBase Database, the EXCEPT and MINUS operators are interchangeable and can both be used to perform set subtraction.

OceanBase Database supports the MERGE EXCEPT DISTINCT and HASH EXCEPT DISTINCT operators for the EXCEPT operator.

MERGE EXCEPT DISTINCT

In the following example, Q1 uses MINUS to join the results of two queries. The c1 column is sortable. The c2 column is not sortable. The MERGE EXCEPT DISTINCT operator is allocated to the No. 0 operator to perform set subtraction and deduplication. Since the c2 column is not sortable, the SORT operator is allocated to the No. 3 operator to sort the data. The operator reads ordered input data from the left and right child operators and performs a merge operation on the ordered input data to generate the difference result and deduplicate the data.

obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected 

obclient> INSERT INTO t1 VALUES(1,1);
Query OK, 1 rows affected 

obclient> INSERT INTO t1 VALUES(2,2);
Query OK, 1 rows affected 

Q1: 
obclient> EXPLAIN SELECT /*+NO_USE_HASH_AGGREGATION*/ c1 FROM t1 MINUS SELECT c2 FROM t1;

Query Plan:
==============================================
|ID|OPERATOR             |NAME|EST. ROWS|COST|
----------------------------------------------
|0 |MERGE EXCEPT DISTINCT|    |2        |77  |
|1 | TABLE SCAN          |T1  |2        |37  |
|2 | SORT                |    |2        |39  |
|3 |  TABLE SCAN         |T1  |2        |37  |
==============================================
Outputs & filters: 
-------------------------------------
  0 - output([MINUS(T1.C1, T1.C2)]), filter(nil)
  1 - output([T1.C1]), filter(nil), 
      access([T1.C1]), partitions(p0)
  2 - output([T1.C2]), filter(nil), sort_keys([T1.C2, ASC])
  3 - output([T1.C2]), filter(nil), 
      access([T1.C2]), partitions(p0)

In the preceding example, the outputs & filters section of the execution plan for the EXCEPT operator shows the following details:

HASH EXCEPT DISTINCT

In the following example, Q2 uses MINUS to join the results of two queries. Sorting is not supported. The HASH EXCEPT DISTINCT operator is allocated to the No. 0 operator to perform set subtraction and deduplication. The operator reads the data from the left child operator to build a hash table and deduplicate the data, and then reads the data from the right child operator to find the difference based on the hash table and deduplicate the data.

Q2: 
obclient> EXPLAIN SELECT c2 FROM t1 MINUS SELECT c2 FROM t1;

Query Plan:
=============================================
|ID|OPERATOR            |NAME|EST. ROWS|COST|
---------------------------------------------
|0 |HASH EXCEPT DISTINCT|    |2        |77  |
|1 | TABLE SCAN         |T1  |2        |37  |
|2 | TABLE SCAN         |T1  |2        |37  |
=============================================
Outputs & filters: 
-------------------------------------
  0 - output([MINUS(T1.C2, T1.C2)]), filter(nil)
  1 - output([T1.C2]), filter(nil), 
      access([T1.C2]), partitions(p0)
  2 - output([T1.C2]), filter(nil), 
      access([T1.C2]), partitions(p0)

In the preceding example, the outputs & filters section of the execution plan for the HASH EXCEPT DISTINCT operator shows the following details, which are the same as those for the MERGE EXCEPT DISTINCT operator.