全部产品
云市场

集合

更新时间:2020-03-27 12:34:43

您可以使用集合运算符 UNIONUNION ALLINTERSECTMINUS 来组合多个查询。 所有集合运算符都具有相同的优先级。如果 SQL 语句包含多个集合运算符,则 OceanBase 从左到右对它们进行判断,除非括号中指定了顺序。

集合运算符的规则和限制

您可以指定 UNION 的属性为 ALLDISTINCTUNIQUE。分别代表集合可重复,和集合不可重复。而其它几种集合操作是不能指定 ALL 属性的(它们只有 DISTINCT 属性)。所有的集合操作默认的属性是 DISTINCT。在 Oceanbase 中,集合操作中可以指定 ORDER BYLIMIT 子句,但是不允许其他子句的出现。我们仅 MINUS,不支持 EXCEPT 尽管这两者的语义是相同的。参加集合操作的各查询结果的列数和相应表达式的数量必须相同,对应的数据类型也必须兼容(例如数值或字符)。

集合运算符规则

如果组件查询是字符数据,则返回值的数据类型如下:

  • 查询两个长度相等的 VARCHAR2 类型值,则返回的值为相同长度的 CHAR 类型。 查询不同长度的 CHAR 类型值,则返回的值为 VARCHAR2 类型,其长度为较大的 CHAR 值。
  • 其中一个或两个查询是 VARCHAR2 数据类型的值,则返回的值为 VARCHAR2 类型。

如果组件查询是数值数据,则返回值的数据类型由数值优先级决定:

  • 查询 BINARY_DOUBLE 类型值,则返回的值为 BINARY_DOUBLE 类型。
  • 查询都选择 BINARY_FLOAT 类型值,则返回的值为 BINARY_FLOAT 类型。
  • 所有查询都选择 NUMBER 类型值,则返回的值为 NUMBER 类型。

在使用集合运算符的查询中,OceanBase 不会跨数据类型组执行隐式转换。如果组件查询的相应表达式同时解析为字符数据和数值数据,OceanBase 将返回错误。

集合运算符限制

集合运算符受到以下限制:

  • 集合运算符在 BLOBCLOB 的列上无效。
  • 如果集合运算符前面的 SELECT 列表包含表达式,则必须为表达式提供列别名,以便在 ORDER BY 子句引用。
  • 您不能用设置的运算符指定 UPDATE 语句。
  • 您不能在这些运算符的子查询中指定 ORDER BY 语句。
  • 不能在包含表集合表达式的 SELECT 语句中使用这些运算符 。

UNION 运算符和 UNION ALL 运算符

UNION 运算符用于合并两个或多个 SELECT 语句的结果集。UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。默认地,UNION 运算符选取不同的值。如果允许重复的值,请使用 UNION ALL

语法

  1. { (< SQL- 查询语句 1>) }
  2. UNION [ALL]
  3. { (< SQL- 查询语句 2>) }

INTERSECT 运算符

返回两个结果集的交集,即两个查询都返回的所有非重复值。

语法

  1. { (< SQL- 查询语句 1>) }
  2. INTERSECT
  3. { (< SQL- 查询语句 2>) }

限制条件

  • 所有查询中的列数和列的顺序必须相同。
  • 比较的两个查询结果集中的列数据类型可以不同但必须兼容。
  • 比较的两个查询结果集中不能包含不可比较的数据类型列(XML、TEXT、NTEXT、IMAGE 或非二进制 CLR 用户定义类型)。
  • 返回的结果集的列名与操作数左侧的查询返回的列名相同。ORDER BY 子句中的列名或别名必须引用左侧查询返回的列名。
  • 不能与 COMPUTECOMPUTE BY 子句一起使用。
  • 通过比较行来确定非重复值时,两个 NULL 值被视为相等。

执行顺序

与表达式中的其他运算符一起使用时的执行顺序:
1、括号中的表达式。
2、INTERSECT 操作数。
3、基于在表达式中的位置从左到右求值的 EXCEPTUNION
如果 EXCEPTINTERSECT 用于比较两个以上的查询集,则数据类型转换是通过一次比较两个查询来确定的,并遵循前面提到的表达式求值规则。

MINUS 运算符

MINUS 运算符返回两个结果集的差,即从左查询中返回右查询没有找到的所有非重复值。

语法

  1. { (< SQL- 查询语句 1>) }
  2. MINUS
  3. { (< SQL- 查询语句 2>) }

示例

以下语句创建了表 table_a 和表 table_b,并向表中插入数据:

  1. CREATE TABLE table_a(PK INT, name VARCHAR(25));
  2. INSERT INTO table_a VALUES(1,'福克斯');
  3. INSERT INTO table_a VALUES(2,'警察');
  4. INSERT INTO table_a VALUES(3,'的士');
  5. INSERT INTO table_a VALUES(4,'林肯');
  6. INSERT INTO table_a VALUES(5,'纽约');
  7. INSERT INTO table_a VALUES(6,'华盛顿');
  8. INSERT INTO table_a VALUES(7,'戴尔');
  9. INSERT INTO table_a VALUES(10,'朗讯');
  10. CREATE TABLE table_b(PK INT, name VARCHAR(25));
  11. INSERT INTO table_b VALUES(1,'福克斯');
  12. INSERT INTO table_b VALUES(2,'警察');
  13. INSERT INTO table_b VALUES(3,'的士');
  14. INSERT INTO table_b VALUES(6,'华盛顿');
  15. INSERT INTO table_b VALUES(7,'戴尔');
  16. INSERT INTO table_b VALUES(8,'微软');
  17. INSERT INTO table_b VALUES(9,'苹果');
  18. INSERT INTO table_b VALUES(11,'苏格兰');

UNION 示例::

  1. SELECT PK, name FROM table_a
  2. UNION
  3. SELECT PK, name FROM table_b;

查询结果如下:

  1. +------+-----------+
  2. | PK | NAME |
  3. +------+-----------+
  4. | 1 | 福克斯 |
  5. | 2 | 警察 |
  6. | 3 | 的士 |
  7. | 4 | 林肯 |
  8. | 5 | 纽约 |
  9. | 6 | 华盛顿 |
  10. | 7 | 戴尔 |
  11. | 10 | 朗讯 |
  12. | 8 | 微软 |
  13. | 9 | 苹果 |
  14. | 11 | 苏格兰 |
  15. +------+-----------+

UNION ALL 示例::

  1. SELECT PK, name FROM table_a
  2. UNION ALL
  3. SELECT PK, name FROM table_b;

查询结果如下:

  1. +------+-----------+
  2. | PK | NAME |
  3. +------+-----------+
  4. | 1 | 福克斯 |
  5. | 2 | 警察 |
  6. | 3 | 的士 |
  7. | 4 | 林肯 |
  8. | 5 | 纽约 |
  9. | 6 | 华盛顿 |
  10. | 7 | 戴尔 |
  11. | 10 | 朗讯 |
  12. | 1 | 福克斯 |
  13. | 2 | 警察 |
  14. | 3 | 的士 |
  15. | 6 | 华盛顿 |
  16. | 7 | 戴尔 |
  17. | 8 | 微软 |
  18. | 9 | 苹果 |
  19. | 11 | 苏格兰 |
  20. +------+-----------+

INTERSECT 示例::

  1. SELECT PK, NAME FROM table_a
  2. INTERSECT
  3. SELECT PK, NAME FROM table_b;

查询结果如下:

  1. +------+-----------+
  2. | PK | NAME |
  3. +------+-----------+
  4. | 1 | 福克斯 |
  5. | 2 | 警察 |
  6. | 3 | 的士 |
  7. | 6 | 华盛顿 |
  8. | 7 | 戴尔 |
  9. +------+-----------+

MINUS 示例

  1. SELECT PK, NAME FROM table_a
  2. MINUS
  3. SELECT PK, NAME FROM table_b;

查询结果如下:

  1. +------+--------+
  2. | PK | NAME |
  3. +------+--------+
  4. | 4 | 林肯 |
  5. | 5 | 纽约 |
  6. | 10 | 朗讯 |
  7. +------+--------+