In OceanBase AI Database, you can use the set operators UNION, UNION ALL, INTERSECT, and MINUS | EXCEPT to combine multiple queries. These set operators have the same precedence. If an SQL statement contains multiple set operators, OceanBase AI Database evaluates them from left to right, unless parentheses are used to explicitly specify the evaluation order.
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 set query syntax, see UNION clause.
The parameters are described as follows:
select_clause_set_left: ASELECTstatement that returns the left set for the set operation.select_clause_set_right: ASELECTstatement that returns the right set for the set operation.UNION: Combines the result sets of two or moreSELECTstatements into one set and removes duplicate rows.UNION ALL: Combines the result sets of two or moreSELECTstatements into one set without removing duplicate rows.INTERSECT: Returns the intersection of the result sets of twoSELECTstatements.MINUS | EXCEPT: Returns the rows in the result set of the firstSELECTstatement that are not included in the result set of the secondSELECTstatement.MINUSis a synonym forEXCEPT.
Notice
Each set operator can only operate on the result sets of SELECT statements, and the number of columns and data types in each result set must be the same.
Examples
Suppose there are two 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, UNION combines the result sets of two SELECT statements into one set, removing duplicate rows.
SELECT id, name FROM test_tbl1
UNION
SELECT id, name FROM test_tbl2;
The query 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, UNION ALL combines 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 query 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, INTERSECT is used to return the intersection of the result sets from the two SELECT statements.
SELECT id, name FROM test_tbl1
INTERSECT
SELECT id, name FROM test_tbl2;
The query result is as follows:
+------+------+
| id | name |
+------+------+
| 1 | A1 |
| 2 | A2 |
| 3 | A3 |
+------+------+
3 rows in set
MINUS example
In the following SQL statement, MINUS or EXCEPT is used to return the rows in the result set of the first SELECT statement that are not included 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 query result is as follows:
+------+------+
| id | name |
+------+------+
| 4 | A4 |
| 5 | A5 |
| 6 | A6 |
+------+------+
3 rows in set