Description
This statement is used to perform set operations on the results of multiple SELECT queries.
Limitations and considerations
The 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 order of operation.
Each set operator can only operate on the result sets of
SELECTstatements, and the number of columns and data types in each result set must be the same.
Syntax
select_stmt
{UNION | UNION ALL | MINUS | EXCEPT | INTERSECT} select_stmt
[, {UNION | UNION ALL | MINUS | EXCEPT | INTERSECT} select_stmt ...]
[ORDER BY order_by_condition_list]
[LIMIT limit_clause];
Parameters
Parameter |
Description |
|---|---|
| select_stmt | The SELECT statement used for set computation. For more information about the SELECT statement, see SELECT. |
| UNION | Used to concatenate the result sets of two SELECT statements into a single set and remove duplicate rows. |
| UNION ALL | Used to concatenate the result sets of two SELECT statements into one set without removing duplicate rows. |
| MINUS \ | EXCEPT | 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. MINUS is a synonym for EXCEPT. |
| INTERSECT | Used to return the intersection of the result sets of two SELECT statements and remove duplicates. |
| ORDER BY order_by_condition_list | Specifies to sort the result set after collection. |
| LIMIT limit_clause | Specifies the number of rows returned after the set operation. |
Examples
Create tables
test_tbl1andtest_tbl2.CREATE TABLE test_tbl1 (col1 INT, col2 INT); CREATE TABLE test_tbl2 (col1 INT, col2 INT);Insert test data into tables
test_tbl1andtest_tbl2.INSERT INTO test_tbl1 VALUES (1, 1), (2, 2), (4, 4); INSERT INTO test_tbl2 VALUES (2, 2), (3, 3), (5, 5);
Select the data in columns
col1andcol2from tablestest_tbl1andtest_tbl2, and use theUNION ALLoperator to merge them into a single result set, retaining duplicate rows.SELECT col1, col2 FROM test_tbl1 UNION ALL SELECT col1, col2 FROM test_tbl2;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 1 | 1 | | 2 | 2 | | 4 | 4 | | 2 | 2 | | 3 | 3 | | 5 | 5 | +------+------+ 6 rows in setSelect the data from columns
col1andcol2in thetest_tbl1andtest_tbl2tables, and use theUNIONoperator to combine them into a single result set, removing duplicate rows.SELECT col1, col2 FROM test_tbl1 UNION SELECT col1, col2 FROM test_tbl2;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 1 | 1 | | 2 | 2 | | 4 | 4 | | 3 | 3 | | 5 | 5 | +------+------+ 5 rows in setSelect the data in columns
col1andcol2from tablestest_tbl1andtest_tbl2, and return their intersection. That is, return the duplicate parts of the data in columnscol1andcol2betweentest_tbl1andtest_tbl2.SELECT col1, col2 FROM test_tbl1 INTERSECT SELECT col1, col2 FROM test_tbl2;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 2 | 2 | +------+------+ 1 row in setSelect the data from columns
col1andcol2in thetest_tbl1table, and exclude the identical data in columnscol1andcol2from thetest_tbl2table. In other words, return the data rows that are present intest_tbl1but absent fromtest_tbl2.SELECT col1, col2 FROM test_tbl1 EXCEPT SELECT col1, col2 FROM test_tbl2;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 1 | 1 | | 4 | 4 | +------+------+ 2 rows in setRetrieve the data from columns
col1andcol2in tablestest_tbl1andtest_tbl2, respectively, and merge them. Then sort the merged result in descending order bycol1and return only the first three rows.SELECT col1, col2 FROM test_tbl1 UNION SELECT col1, col2 FROM test_tbl2 ORDER BY col1 DESC LIMIT 3;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 5 | 5 | | 4 | 4 | | 3 | 3 | +------+------+ 3 rows in set