UNION 子句

描述

该语句用于对多个 SELECT 查询的结果进行 UNION、EXCEPT或 INTERSECT 操作。

语法

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

参数解释

示例

以如下两表的数据为例：

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);

• 查询数据集 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 
  

• 查询数据集 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  
  

• 查询 t1 与 t2 的交集。

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

• 查询 t1 与 t2 的差集。

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

• 查询 t1 和 t2 并集中按 c2 降序排序的前两行。

  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