Set operations

In the Oracle compatible mode of OceanBase Cloud, you can use set operators such as UNION, UNION ALL, INTERSECT, and MINUS to combine multiple queries. These set operators have the same priority. If an SQL statement contains multiple set operators, OceanBase Cloud evaluates them from left to right, unless the order of operations is explicitly specified using parentheses.

This topic describes how to use the set operators UNION, UNION ALL, INTERSECT, and MINUS.

Syntax

select_clause_set_left 
{ UNION | UNION ALL | MINUS | INTERSECT}
select_clause_set_right

where

• select_clause_set_left is the SELECT statement that returns the left set used for set calculation.

• select_clause_set_right is the SELECT statement that returns the right set used for set calculation.

• UNION combines the result sets of two or more SELECT statements into one set and removes duplicate rows.

• UNION ALL combines the result sets of two or more SELECT statements into one set without removing duplicate rows.

• INTERSECT returns the intersection of the result sets of two SELECT statements.

• MINUS returns rows in the result set of the first SELECT statement that are not contained in the result set of the second SELECT statement.

  Note

  OceanBase Cloud in the Oracle compatible mode supports only the MINUS operator and does not support the EXCEPT operator, though they are semantically equivalent.

Examples

Suppose we have test tables test_tbl1 and test_tbl2 with the following data:

obclient [SYS]> SELECT * FROM test_tbl1;
+------+------+
| ID   | NAME |
+------+------+
|    1 | A1   |
|    2 | A2   |
|    3 | A3   |
|    4 | A4   |
|    5 | A5   |
|    6 | A6   |
+------+------+
6 rows in set

obclient [SYS]> SELECT * FROM test_tbl2;
+------+------+
| ID   | NAME |
+------+------+
|    1 | A1   |
|    2 | A2   |
|    3 | A3   |
|    7 | A7   |
|    8 | A8   |
|    9 | A9   |
+------+------+
6 rows in set

UNION example

In the following SQL statement, the UNION operator is used to combine the result sets of two SELECT statements into one set and remove duplicate rows.

SELECT id, name FROM test_tbl1
UNION
SELECT id, name FROM test_tbl2;

The return result is as follows:

+------+------+
| ID   | NAME |
+------+------+
|    1 | A1   |
|    2 | A2   |
|    3 | A3   |
|    4 | A4   |
|    5 | A5   |
|    6 | A6   |
|    7 | A7   |
|    8 | A8   |
|    9 | A9   |
+------+------+
9 rows in set

UNION ALL example

In the following SQL statement, the UNION ALL operator is used to combine the result sets of two SELECT statements into one set without removing duplicate rows.

SELECT id, name FROM test_tbl1
UNION ALL
SELECT id, name FROM test_tbl2;

The return result is as follows:

+------+------+
| ID   | NAME |
+------+------+
|    1 | A1   |
|    2 | A2   |
|    3 | A3   |
|    4 | A4   |
|    5 | A5   |
|    6 | A6   |
|    1 | A1   |
|    2 | A2   |
|    3 | A3   |
|    7 | A7   |
|    8 | A8   |
|    9 | A9   |
+------+------+
12 rows in set

INTERSECT example

In the following SQL statement, the INTERSECT operator is used to return the intersection of the result sets of two SELECT statements.

SELECT id, name FROM test_tbl1
INTERSECT
SELECT id, name FROM test_tbl2;

The return result is as follows:

+------+------+
| ID   | NAME |
+------+------+
|    1 | A1   |
|    2 | A2   |
|    3 | A3   |
+------+------+
3 rows in set

MINUS example

In the following SQL statement, the MINUS operator is used to return the rows in the result set of the first SELECT statement that are not contained in the result set of the second SELECT statement.

SELECT id, name FROM test_tbl1
MINUS
SELECT id, name FROM test_tbl2;

The return result is as follows:

+------+------+
| ID   | NAME |
+------+------+
|    4 | A4   |
|    5 | A5   |
|    6 | A6   |
+------+------+
3 rows in set