EXCEPT/MINUS

The EXCEPT operator calculates the difference between the sets of data returned by the left and right child operators and removes duplicate data.

In Oracle mode, the MINUS operator is generally used to calculate the difference between sets of data, whereas in MySQL mode, the EXCEPT operator is generally used. In MySQL mode of OceanBase Database, the EXCEPT and MINUS operators are interchangeable and can both be used to calculate the difference between sets of data.

OceanBase Database supports the MERGE EXCEPT DISTINCT and HASH EXCEPT DISTINCT forms of the EXCEPT operator.

MERGE EXCEPT DISTINCT

In the example below, Q1 uses the MINUS operator to join the results of two queries. The c1 column can be sorted, and the SORT operator is assigned to operator 3 to sort the data in the c2 column. The operator reads ordered data from the left and right child operators and calculates the difference and deduplicate the data through a MERGE process.

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 execution plan shown above, the outputs & filters section of operator 1 lists the output information of the EXCEPT operator. The following table describes the fields in this section:

HASH EXCEPT DISTINCT

In the example below, Q2 uses the MINUS operator to join the results of two queries. The data in any column cannot be sorted. The HASH EXCEPT DISTINCT operator is used at operator 0 to calculate the difference and deduplicate the data. The operator reads data from the left child operator to build a hash table and deduplicate the data, and then reads data from the right child operator to calculate the difference based on the hash table and deduplicate the result.

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 execution plan shown above, the outputs & filters section of operator 1 lists the output information of the HASH EXCEPT DISTINCT operator. The preceding table describes the fields in this section.