EXCEPT/MINUS

EXCEPT operators perform a difference-set operation on the output of the left and right suboperators and deduplicate the result.

Generally, the MINUS operator is used in Oracle mode to perform difference set operations, whereas EXCEPT operators are used in MySQL mode to perform difference set operations. In OceanBase Database, however, both EXCEPT and MINUS operators can be used for difference-set operations in MySQL mode.

OceanBase Database supports the following types of EXCEPT operators: MERGE EXCEPT DISTINCT and HASH EXCEPT DISTINCT.

MERGE EXCEPT DISTINCT

In the following example, Q1 uses the MINUS operator to connect the two queries, a sorting method is available for c1, and the MERGE EXCEPT DISTINCT operator is used as the No. 0 operator to obtain the difference set and deduplicate the result. No sorting method is available for c2, so a SORT operator is assigned before the No. 3 operator for sorting. When these operators are executed, they read ordered inputs from the left and right subnodes and merge and deduplicate the rows to generate the difference 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 c1 FROM t1 MINUS SELECT c2 FROM t1\G
*************************** 1. row ***************************
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 in the execution plan display shows in detail the output information of the MERGE EXCEPT DISTINCT operator.

HASH EXCEPT DISTINCT

In the following example, Q2 uses the MINUS operator to connect the two queries, no sorting method is available, and the HASH EXCEPT DISTINCT operator is used as the No. 0 operator to generate the difference set and deduplicate the result. When the operator is being executed, it first reads the output of the left subnode to create a hash table and removes duplicate rows. Then, it reads the output of the right subnode and uses the hash table to generate the difference set and deduplicate the result.

Q2:
obclient> EXPLAIN SELECT c2 FROM t1 MINUS SELECT c2 FROM t1\G
*************************** 1. row ***************************
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 in the execution plan display shows in detail the output information of the HASH EXCEPT DISTINCT operator. The fields of the operator have the same meaning as those of the MERGE EXCEPT DISTINCT operator.