EXCEPT/MINUS

The EXCEPT operator calculates the difference between the result sets of the left and right child operators and removes duplicate values.

In Oracle mode, the MINUS operator is generally used to calculate the difference between result sets, 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 be used to calculate the difference between result sets.

OceanBase Database supports the MERGE EXCEPT DISTINCT and HASH EXCEPT DISTINCT forms of 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 calculate the difference and remove duplicates. A SORT operator is allocated to the No. 3 operator to sort the data because no sorting is available for the c2 column. The operator reads ordered data from the left and right child operators and calculates the difference and removes duplicates through a MERGE process, thereby obtaining the final result set.

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 displays the output information of the EXCEPT operator. The EXCEPT operator in this example is in MySQL mode of OceanBase Database, which supports both the EXCEPT and MINUS operators. The following table describes the parameters in the outputs & filters section:

HASH EXCEPT DISTINCT

In the following example, Q2 uses MINUS to join the results of two queries. No sorting is available. The HASH EXCEPT DISTINCT operator is allocated to the No. 0 operator to calculate the difference and remove duplicates. The operator reads the output of the left child operator to build a hash table and removes duplicates. Then, it reads the output of the right child operator to calculate the difference based on the hash table and remove duplicates.

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)

The outputs & filters section of the execution plan displays the output information of the HASH EXCEPT DISTINCT operator. The fields in the outputs & filters section for the HASH EXCEPT DISTINCT operator are the same as those for the MERGE EXCEPT DISTINCT operator.