UNION clause

Purpose

You can use this statement to perform the UNION, EXCEPT, or INTERSECT operation on the query results of the SELECT statement.

Syntax

select_clause_set:
    select_clause_set_left [ UNION | UNION ALL | MINUS | EXCEPT | INTERSECT] select_clause_set_right
  [sort_list_columns] [limit_clause]

Parameters

Examples

Take the following two tables as an example:

obclient> CREATE TABLE t1 (c1 INT, c2 INT);
obclient> CREATE TABLE t2 (c1 INT, c2 INT);
obclient> INSERT INTO t1 VALUES (1, -1), (2, -2);
obclient> INSERT INTO t2 VALUES (1, 1), (2, -2), (3, 3);

• Query all records of dataset t1∪t2.

  obclient> SELECT c1, c2 FROM t1 UNION ALL SELECT c1, c2 FROM t2;
  +------+------+
  | c1   | c2   |
  +------+------+
  |    1 |   -1 |
  |    2 |   -2 |
  |    1 |    1 |
  |    2 |   -2 |
  |    3 |    3 |
  +------+------+
  5 rows in set
  

• Query all unique records of dataset t1∪t2.

  obclient> SELECT c1, c2 FROM t1 UNION SELECT c1, c2 FROM t2;
  +------+------+
  | c1   | c2   |
  +------+------+
  |    1 |   -1 |
  |    2 |   -2 |
  |    1 |    1 |
  |    3 |    3 |
  +------+------+
  4 rows in set  
  

• Query the intersection of t1 and t2.

  obclient> SELECT c1, c2 FROM t1 INTERSECT SELECT c1, c2 FROM t2;
  +------+------+
  | c1   | c2   |
  +------+------+
  |    2 |   -2 |
  +------+------+
  1 row in set
  

• Query the records that exist in only one of t1 and t2.

  obclient> SELECT c1, c2 FROM t1 EXCEPT SELECT c1, c2 FROM t2;
  +------+------+
  | c1   | c2   |
  +------+------+
  |    1 |   -1 |
  +------+------+
  1 row in set
  

• Query the first two rows sorted in descending order by c2 in the union of t1 and t2.

  obclient> SELECT c1, c2 FROM t1 UNION SELECT c1, c2 FROM t2 ORDER BY c2 DESC LIMIT 2;
  +------+------+
  | c1   | c2   |
  +------+------+
  |    3 |    3 |
  |    1 |    1 |
  +------+------+
  2 rows in set