Set operations

In the MySQL-compatible mode of OceanBase Database, you can use set operators such as UNION, UNION ALL, INTERSECT, and MINUS | EXCEPT to combine multiple queries. These set operators have the same priority. If an SQL statement contains multiple set operators, OceanBase Database calculates 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

For more information about the syntax of set-based queries, see UNION clause.

Parameter description:

• 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 | EXCEPT returns rows in the result set of the first SELECT statement that are not contained in the result set of the second SELECT statement. MINUS is a synonym of EXCEPT.

Examples

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

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

obclient [test]> 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, we use UNION to combine the result sets of two SELECT statements into one set and remove duplicate rows in the merged result set.

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

The 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, we use UNION ALL 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 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, we use INTERSECT 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 result is as follows:

+------+------+
| id   | name |
+------+------+
|    1 | A1   |
|    2 | A2   |
|    3 | A3   |
+------+------+
3 rows in set

MINUS example

In the following SQL statement, we use MINUS or EXCEPT 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;

or

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

The result is as follows:

+------+------+
| id   | name |
+------+------+
|    4 | A4   |
|    5 | A5   |
|    6 | A6   |
+------+------+
3 rows in set

References

UNION clause