数据库中的集合操作可以把多个查询的结果组合成一个结果集。集合操作主要包含：

  • UNION

  • INTERSECT

  • EXCEPT/MINUS

这里需要注意的是参加集合操作的各查询结果的列数必须相同，对应的数据类型也必须兼容。对于 UNION 来说用户可以指定 UNION的属性为 ALL 和 DISTINCT/UNIQUE。分别代表集合可重复，和集合不可重复。而其它几种集合操作是不能指定 ALL 属性的（它们只有 DISTINCT 属性）。所有的集合操作默认的属性是 DISTINCT。在 Oceanbase 数据库中，集合操作中可以指定 ORDER BY 和 LIMIT 子句，但是不允许其他子句的出现，如下所示：

  1. obclient> create table t1(a int primary key, b int, c int);
  2. Query OK, 0 rows affected (0.16 sec)
  3. obclient> create table t2(a int primary key, b int, c int);
  4. Query OK, 0 rows affected (0.10 sec)
  6. --支持 union 语句中出现 order by 和 limit 子句
  7. obclient> (select * from t1 union all select * from t2) order by a limit 10;
  8. Empty set (0.02 sec)
  10. --不支持 union 语句中出现除 order by 和 limit 子句的其他子句，比如 group by
  11. obclient> (select * from t1 union all select * from t2) group by a limit 10;
  12. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'group by a limit 10' at line 1

UNION 例子

该例子获取 t1 和 t2 中所有不重复的行。

  1. obclient> create table t1(a int, b int, c int);
  2. Query OK, 0 rows affected (0.12 sec)
  3. obclient> create table t2(a int, b int, c int);
  4. Query OK, 0 rows affected (0.11 sec)
  5. obclient> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
  6. Query OK, 3 rows affected (0.07 sec)
  7. Records: 3  Duplicates: 0  Warnings: 0
  8. obclient> insert into t2 values (2,2,2),(3,3,3),(4,4,4);
  9. Query OK, 3 rows affected (0.02 sec)
  10. Records: 3  Duplicates: 0  Warnings: 0
  11. obclient> select * from t1 union select * from t2;
  12. +------+------+------+
  13. | a    | b    | c    |
  14. +------+------+------+
  15. |    1 |    1 |    1 |
  16. |    2 |    2 |    2 |
  17. |    3 |    3 |    3 |
  18. |    4 |    4 |    4 |
  19. +------+------+------+
  20. 4 rows in set (0.01 sec)

UNION All 例子

该例子获取 t1 和 t2 中的所有行，不进行去重。

  1. obclient> create table t1(a int, b int, c int);
  2. Query OK, 0 rows affected (0.12 sec)
  3. obclient> create table t2(a int, b int, c int);
  4. Query OK, 0 rows affected (0.11 sec)
  5. obclient> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
  6. Query OK, 3 rows affected (0.07 sec)
  7. Records: 3  Duplicates: 0  Warnings: 0
  8. obclient> insert into t1 values (2,2,2),(3,3,3),(4,4,4);
  9. Query OK, 3 rows affected (0.02 sec)
  10. Records: 3  Duplicates: 0  Warnings: 0
  11. obclient> select * from t1 union all select * from t2;
  12. +------+------+------+
  13. | a    | b    | c    |
  14. +------+------+------+
  15. |    1 |    1 |    1 |
  16. |    2 |    2 |    2 |
  17. |    3 |    3 |    3 |
  18. |    2 |    2 |    2 |
  19. |    3 |    3 |    3 |
  20. |    4 |    4 |    4 |
  21. +------+------+------+
  22. 6 rows in set (0.02 sec)

INTERSECT 例子

该例子获取同时出现在 t1 和 t2 中的行，并且去重。

  1. obclient> create table t1(a int, b int, c int);
  2. Query OK, 0 rows affected (0.12 sec)
  3. obclient> create table t2(a int, b int, c int);
  4. Query OK, 0 rows affected (0.12 sec)
  5. obclient> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
  6. Query OK, 3 rows affected (0.02 sec)
  7. Records: 3  Duplicates: 0  Warnings: 0
  8. obclient> insert into t2 values (2,2,2),(3,3,3),(3,3,3),(4,4,4);
  9. Query OK, 4 rows affected (0.01 sec)
  10. Records: 4  Duplicates: 0  Warnings: 0
  11. obclient> select * from t1 intersect select * from t2;
  12. +------+------+------+
  13. | a    | b    | c    |
  14. +------+------+------+
  15. |    2 |    2 |    2 |
  16. |    3 |    3 |    3 |
  17. +------+------+------+
  18. 2 rows in set (0.01 sec)

EXCEPT/MINUS 例子

该例子获取出现在 t1 中，但是不出现在 t2 中的行，并且去重。

  1. obclient> create table t1(a int, b int, c int);
  2. Query OK, 0 rows affected (0.12 sec)
  3. obclient> create table t2(a int, b int, c int);
  4. Query OK, 0 rows affected (0.12 sec)
  5. obclient> insert into t1 values (1,1,1),(2,2,2),(3,3,3);
  6. Query OK, 3 rows affected (0.02 sec)
  7. Records: 3  Duplicates: 0  Warnings: 0
  8. obclient> insert into t2 values (2,2,2),(3,3,3),(3,3,3),(4,4,4);
  9. Query OK, 4 rows affected (0.01 sec)
  10. Records: 4  Duplicates: 0  Warnings: 0
  11. obclient> select * from t1 except select * from t2;
  12. +------+------+------+
  13. | a    | b    | c    |
  14. +------+------+------+
  15. |    1 |    1 |    1 |
  16. +------+------+------+
  17. 1 row in set (0.02 sec)