All Products
Search
Document Center

EXCEPT/MINUS

Last Updated: Jun 18, 2021

The EXCEPT operator performs 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 the EXCEPT operator is 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.

EXCEPT operators supported by OceanBase Database include MERGE EXCEPT DISTINCT and HASH EXCEPT DISTINCT.

MERGE EXCEPT DISTINCT

In the following example, Q1 uses the MINUS operator to connect the two queries. Because a sorting method is available for c1, the MERGE EXCEPT DISTINCT operator is used as the No. 0 operator to obtain the difference set and deduplicate the result. Because no sorting method is available for c2, 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 (0.12 sec)

obclient>INSERT INTO t1 VALUES(1,1);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(2,2);
Query OK, 1 rows affected (0.12 sec)

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 demo shows in detail the output information of the MERGE EXCEPT DISTINCT operator.

Field

Description

output

The output expression of the operator.

The respective output of the two suboperators connected by using EXCEPT or MINUS. MINUS is used in Oracle mode, and EXCEPT in MySQL mode. It indicates a column in the output of the difference set operation. Within the brackets are the output columns of the left and right subnodes corresponding to this column.

filter

The filter conditions of the operator.

In this example, the condition is set to nil because no filter condition is configured for the MERGE EXCEPT DISTINCT operator.

HASH EXCEPT DISTINCT

In the following example, Q2 uses the MINUS operator to connect the two queries. Because no sorting method is available, 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 demo shows in detail the output information of the HASH EXCEPT DISTINCT operator. Fields of the operator have the same meaning as those of the MERGE EXCEPT DISTINCT operator.