The UNION operator is used to perform a union operation on the result sets of two queries.
OceanBase AI Database supports the UNION, UNION ALL, HASH UNION DISTINCT, and MERGE UNION DISTINCT operators.
UNION ALL
UNION ALL directly merges and outputs the result sets of two queries.
In the following example, query Q1 uses UNION ALL to combine the result sets of the two queries. During execution, the UNION ALL operator outputs all results from the left and right child nodes in sequence.
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 UNION ALL SELECT c2 FROM t1;
Query Plan:
====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
------------------------------------
|0 |UNION ALL | |4 |74 |
|1 | TABLE SCAN|T1 |2 |37 |
|2 | TABLE SCAN|T1 |2 |37 |
====================================
Outputs & filters:
-------------------------------------
0 - output([UNION(T1.C1, T1.C2)]), filter(nil)
1 - output([T1.C1]), filter(nil),
access([T1.C1]), 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 shows the output information of the UNION ALL operator as follows:
Information name |
Description |
|---|---|
| output | The output expressions of this operator. |
| filter | The filter predicate of this operator. In the example, no filter is set for the UNION ALL operator, so the value is nil. |
MERGE UNION DISTINCT
MERGE UNION DISTINCT performs a union operation on the result sets, removes duplicates, and then outputs the result.
In the following example, query Q2 uses UNION DISTINCT to combine the result sets of the two queries. Column c1 provides an available sort key. Operator 0 generates a MERGE UNION DISTINCT operator to compute the union and remove duplicates. Because column c2 lacks an available sort key, a SORT operator is assigned to Operator 3. During execution, the operator reads the ordered inputs from the left and right child nodes, merges them to obtain an ordered output, and removes duplicates.
Q2:
obclient> EXPLAIN SELECT c1 FROM t1 UNION SELECT c2 FROM t1;
Query Plan:
=============================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
---------------------------------------------
|0 |MERGE UNION DISTINCT| |4 |77 |
|1 | TABLE SCAN |T1 |2 |37 |
|2 | SORT | |2 |39 |
|3 | TABLE SCAN |T1 |2 |37 |
=============================================
Outputs & filters:
-------------------------------------
0 - output([UNION(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)
The outputs & filters section of the execution plan for the preceding example shows the output information of the MERGE UNION DISTINCT operator. The field meanings are the same as those for the UNION ALL operator.
HASH UNION DISTINCT
HASH UNION DISTINCT performs a union operation on the result sets, removes duplicates, and then outputs the result.
In the following example, query Q3 uses UNION DISTINCT to combine the result sets of the two queries. No sort key is available, so Operator 0 uses HASH UNION DISTINCT to compute the union and remove duplicates. During execution, the operator reads the outputs from the left and right child nodes, builds a hash table to remove duplicates, and finally outputs the deduplicated result.
Q3:
obclient> EXPLAIN SELECT c2 FROM t1 UNION SELECT c2 FROM t1;
Query Plan:
============================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
--------------------------------------------
|0 |HASH UNION DISTINCT| |4 |77 |
|1 | TABLE SCAN |T1 |2 |37 |
|2 | TABLE SCAN |T1 |2 |37 |
============================================
Outputs & filters:
-------------------------------------
0 - output([UNION(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 for the preceding example shows the output information of the HASH UNION DISTINCT operator. The field meanings are the same as those for the UNION ALL operator.