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 then removes duplicate rows.

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 can be sorted, so the c1 column of the 0th operator is sorted through the SORT operator to remove duplicates and calculate the difference. Since the c2 column cannot be sorted, the c2 column of the 3rd operator is sorted to ensure the order of input for difference calculation. The operator reads ordered inputs from the left and right child operators and performs a MERGE operation on the ordered inputs to remove duplicates and calculate the difference.

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 information about its output:

HASH EXCEPT DISTINCT

In the following example, Q2 uses MINUS to join the results of two queries. The operator cannot sort any column. The 0th operator uses the HASH EXCEPT DISTINCT operator to remove duplicates and calculate the difference. The operator reads the output of the left child operator to build a hash table and then reads the output of the right child operator to calculate the difference based on the hash table while removing 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)

In the preceding example, the outputs & filters section of the execution plan for the HASH EXCEPT DISTINCT operator shows the following information about its output, which is the same as that for the MERGE EXCEPT DISTINCT operator.