使用集合运算符

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 DUAL
UNION
SELECT '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 employees
UNION
SELECT DEPARTMENT_ID, LOCATION_ID
FROM 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;