开发手册 欢迎您！
软件开发者资料库

使用集合运算符

使用集合运算符 - Oracle数据库11g管理员认证助理，OPN认证专家考试的准备指南。本指南可帮助您逐步在线准备证书考试，并且完全免费。此认证旨在使您获得赢得基于Oracle SQL的项目的简短列表。 Oracle技术认证是一种有价值的，行业认可的凭证，表明已经证明的知识水平和技能。

Set运算符用于连接两个(或多个)SELECT语句的结果.Oracle 11g中可用的SET运算符是UNION，UNION ALL，INTERSECT和MINUS.

UNION集合运算符返回两个SELECT语句的组合结果.实际上，它从结果中删除重复项，即每个重复结果只列出一行.为了抵消这种行为，使用UNION ALL集合运算符，它在最终结果中保留重复项.INTERSECT仅列出两个SELECT查询共有的记录;如果在第一个查询的结果中也找到了第二个查询的结果，则MINUS集合运算符会从输出中删除第二个查询的结果. INTERSECT和MINUS集合操作产生不重复的结果.

所有SET运算符在它们之间共享相同的优先级.相反，在查询执行期间，Oracle从左到右或从顶部开始评估如果使用明确的括号，则顺序可能会有所不同，因为括号将优先于悬空运算符.

要记住的点 -

  • 所有参与的SELECT语句必须选择相同数量的列.显示中使用的列名取自第一个查询.

  • 列列表的数据类型必须与oracle兼容/可隐式转换.如果组件查询中的相应列属于不同的数据类型组，Oracle将不执行隐式类型转换.例如，如果第一个组件查询中的列是数据类型DATE，而第二个组件查询中的相应列是数据键入CHAR，Oracle不会执行隐式转换，但会引发ORA-01790错误.

  • 必须使用位置排序对结果集进行排序. Set运算符不允许单独的结果集排序. ORDER BY可以在查询结束时出现一次.例如，

  • UNION和INTERSECT运算符是可交换的，即查询的顺序并不重要;它不会改变最终结果.

  • 性能方面，与UNION相比，UNION ALL显示出更好的性能，因为在过滤重复项和排序结果集时不会浪费资源.

  • 集合运算符可以是子查询的一部分.

  • 集合运算符不能在SELECT语句中使用包含TABLE集合表达式.

  • 不允许在Set运算符中使用LONG，BLOB，CLOB，BFILE，VARRAY或嵌套表.对于update子句，不允许使用set运算符.

UNION

使用UNION连接多个SELECT查询时运算符，Oracle在删除所有重复项并按排序顺序(默认为升序)后显示所有复合SELECT查询的组合结果，而不忽略NULL值.

考虑使用UNION运算符连接的以下五个查询.最终的组合结果集包含来自所有SQL的值.请注意重复删除和数据排序.

 SELECT 1 NUM FROM DUAL  UNION  SELECT 5 FROM DUAL  UNION  SELECT 3 FROM DUAL  UNION  SELECT 6 FROM DUAL  UNION  SELECT 3 FROM DUAL;  NUM  -------  1  3  5  6

需要注意的是，SELECT查询中选择的列必须是兼容的数据类型.当违反规则时，Oracle会抛出错误消息.

 SELECT TO_DATE('12-OCT-03') FROM DUALUNIONSELECT '13-OCT-03' FROM DUAL;SELECT TO_DATE('12-OCT-03') FROM DUAL       *ERROR at line 1:ORA-01790: expression must have same datatype as corresponding expression

UNION ALL

UNION和UNION ALL的功能相似，略有不同.但是UNION ALL会在不删除重复和排序数据的情况下提供结果集.例如，在上面的查询中，UNION被UNION ALL替换以查看效果.

考虑UNION部分中演示的查询.请注意在没有排序和重复数据删除的情况下生成的输出的差异.

 SELECT 1 NUM FROM DUAL  UNION ALL  SELECT 5 FROM DUAL  UNION ALL  SELECT 3 FROM DUAL  UNION ALL  SELECT 6 FROM DUAL  UNION ALL  SELECT 3 FROM DUAL;  NUM  -------  1  5  3  6  3

INTERSECT

使用INTERSECT运算符，Oracle显示两个SELECT语句中的公共行，没有重复项和数据按排序顺序排列(默认为升序) .

例如，下面的SELECT查询检索部门10和20中常见的工资.根据ISO SQL标准，INTERSECT优先于其他设置运算符的评估，但是这个尚未归Oracle所有.

 SELECT SALARY  FROM employees  WHERE DEPARTMENT_ID = 10  INTRESECT  SELECT SALARY  FROM employees  WHERE DEPARTMENT_ID = 20  SALARY  ---------  1500  1200  2000

MINUS

减号运算符显示第一个查询中存在但在第二个查询中不存在的行，没有重复项和数据按升序排列默认.

 SELECT JOB_ID  FROM employees  WHERE DEPARTMENT_ID = 10  MINUS  SELECT JOB_ID  FROM employees  WHERE DEPARTMENT_ID = 20;  JOB_ID  -------------  HR  FIN  ADMIN

匹配SELECT语句

可能存在复合SELECT语句可能具有所选列的不同计数和数据类型的情况.因此，为了显式匹配列列表，在缺少的位置插入NULL列，以匹配每个SELECT语句中所选列的计数和数据类型.对于数字列，零也可以替换为匹配查询中所选列的类型.

在下面的查询中，员工姓名(varchar2)和位置ID的数据类型(数字)不匹配.因此，执行以下查询会因兼容性问题而引发错误.

SELECT DEPARTMENT_ID "Dept", first_name "Employee"FROM employeesUNIONSELECT DEPARTMENT_ID, LOCATION_IDFROM departments;ERROR at line 1:ORA-01790: expression must have same datatype as corresponding expression

显式地，列可以匹配将NULL替换为位置ID和员工名称.

 SELECT DEPARTMENT_ID"Dept"，first_name"Employee"，NULL"Location" FROM employees  UNION  SELECT DEPARTMENT_ID，NULL"Employee"，LOCATION_ID  FROM departments;

在SET操作中使用ORDER BY子句

ORDER BY子句只能在包含复合SELECT语句的查询结尾出现一次.这意味着单个SELECT语句不能包含ORDER BY子句.此外，排序可以基于仅出现在第一个SELECT查询中的列.因此，建议使用列位置对复合查询进行排序.

下面的compund查询统一了SALARY列的两个部门和排序的结果.

 SELECT employee_id，first_name， salary  FROM employees  WHERE department_id = 10  UNION  SELECT employee_id，first_name，salary  FROM employees  WHERE department_id = 20  ORDER BY 3;