All Products
Search
Document Center

INTERSECT

Last Updated: Jun 18, 2021

The INTERSECT operator perform an intersection-set operation on the output of the left and right suboperators and deduplicates the result.

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

MERGE INTERSECT DISTINCT

In the following example, Q1 connects the two queries with the INTERSECT operator. Because a sorting method is available for c1, the MERGE INTERSECT DISTINCT operator is used as the No. 0 operator to obtain the intersection 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 obtain the intersection 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 INTERSECT SELECT c2 FROM t1\G;
*************************** 1. row ***************************
Query Plan:
=================================================
|ID|OPERATOR                |NAME|EST. ROWS|COST|
-------------------------------------------------
|0 |MERGE INTERSECT DISTINCT|    |2        |77  |
|1 | TABLE SCAN             |T1  |2        |37  |
|2 | SORT                   |    |2        |39  |
|3 |  TABLE SCAN            |T1  |2        |37  |
=================================================
Outputs & filters: 
-------------------------------------
  0 - output([INTERSECT(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 INTERSECT DISTINCT operator.

Field

Description

output

The output expression of the operator.

The respective output of the two suboperators connected by using the MERGE INTERSECT DISTINCT operator. It indicates a column in the output of the intersection-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 INTERSECT DISTINCT operator.

HASH INTERSECT DISTINCT

In the following example, Q2 uses the INTERSECT operator to connect the two queries. Because no sorting method is available, the HASH INTERSECT DISTINCT operator is used as the No. 0 operator to obtain the intersection set and deduplicate the result. When the operator is being executed, it first reads the output of the subnode on one side to create a hash table and removes duplicate rows. Then, it reads the output of the subnode on the other side, and uses the hash table to generate the difference set and deduplicate the result.

Q2: 
obclient>EXPLAIN SELECT c2 FROM t1 INTERSECT SELECT c2 FROM t1\G;
*************************** 1. row ***************************
Query Plan:
================================================
|ID|OPERATOR               |NAME|EST. ROWS|COST|
------------------------------------------------
|0 |HASH INTERSECT DISTINCT|    |2        |77  |
|1 | TABLE SCAN            |T1  |2        |37  |
|2 | TABLE SCAN            |T1  |2        |37  |
================================================

Outputs & filters: 
-------------------------------------
  0 - output([INTERSECT(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 INTERSECT DISTINCT operator. Fields of the operator have the same meaning as that of the MERGE INTERSECT DISTINCT operator.