UNION clause

Purpose

The statement is used to perform set operations on the results of multiple SELECT queries.

Limitations and considerations

• Set operators have the same precedence. If an SQL statement contains multiple set operators, OceanBase Database will calculate them from left to right unless parentheses are used to specify the order of operations.

• Each set operator can operate only on the result set of a SELECT statement, and the result sets must have the same number of columns and data types.

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

Examples

1. Create tables test_tbl1 and test_tbl2.

   CREATE TABLE test_tbl1 (col1 INT, col2 INT);
   CREATE TABLE test_tbl2 (col1 INT, col2 INT);
   

2. Insert test data into tables test_tbl1 and test_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 from the col1 and col2 columns of tables test_tbl1 and test_tbl2, and merge the results into one set using the UNION ALL operator. Duplicate rows are retained.

  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 set
  

• Select the data from the col1 and col2 columns of tables test_tbl1 and test_tbl2, and merge the results into one set using the UNION operator. Duplicate rows are removed.

  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 set
  

• Select the data from the col1 and col2 columns of tables test_tbl1 and test_tbl2, and return the intersection of the two sets. That is, return the duplicate rows in the col1 and col2 columns of test_tbl1 and test_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 set
  

• Select the data from the col1 and col2 columns of test_tbl1, and exclude the data from the col1 and col2 columns of test_tbl2. That is, return the rows in test_tbl1 that do not exist in test_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 set
  

• Obtain the data from the col1 and col2 columns of test_tbl1 and test_tbl2, merge the data, sort the merged data in descending order by col1, and return 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
  

References

• SELECT
• Set operations