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 duplicate rows from the result set.

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 both can 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, so the c1 column values are sorted in ascending order. The c2 column is not sortable. Therefore, the SORT operator is allocated on the 3rd operator to sort the c2 column values in ascending order. The system reads ordered inputs from the left and right child operators and performs a merge operation on the ordered inputs to eliminate duplicates and obtain the difference 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 details pane lists the output information of the EXCEPT operator as follows:

HASH EXCEPT DISTINCT

In the following example, Q2 uses MINUS to join the results of two queries. Sorting is unavailable for either column. Therefore, the HASH EXCEPT DISTINCT operator is used on the 0th operator to eliminate duplicates and obtain the difference result set. The system reads the output of the left child operator to build a hash table and then reads the output of the right child operator to find the difference based on the hash table and eliminate 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 details pane for the preceding example lists the output information of the HASH EXCEPT DISTINCT operator, and the parameters and their descriptions are the same as those for the MERGE EXCEPT DISTINCT operator.