使用集合运算符
使用集合运算符
Set 运算符用于连接两个（或多个）SELECT 语句的结果。Oracle 11g 中可用的 SET 运算符是 UNION、UNION ALL、INTERSECT 和 MINUS。
UNION 集合运算符返回两个 SELECT 语句的组合结果。本质上，它从结果中删除重复项，即每个重复的结果只列出一行。要对抗这种行为，请使用 UNION ALL 集合运算符，它保留了重复项最终结果。INTERSECT 只列出两个 SELECT 查询共有的记录；如果在第一个查询的结果中也找到了第二个查询的结果，则 MINUS 集合运算符将从输出中删除第二个查询的结果。INTERSECT 和 MINUS 集合操作产生不重复的结果。
所有的 SET 运算符在它们之间共享相同的优先级。相反，在查询执行期间，Oracle 从左到右或从上到下开始计算。如果明确使用括号，则顺序可能不同，因为括号将优先于悬空运算符。
要记住的要点 –
-
所有参与的 SELECT 语句必须选择相同数量的列。显示中使用的列名取自第一个查询。
-
列列表的数据类型必须兼容/可被 oracle 隐式转换。如果组件查询中对应的列属于不同的数据类型组，Oracle 不会进行隐式类型转换。例如，如果第一个组件查询中的列是数据类型的，而第二个组件查询中对应的列是数据类型类型 CHAR，Oracle 不会执行隐式转换，但会引发 ORA-01790 错误。
-
必须使用位置排序对结果集进行排序。Set 运算符不允许单独的结果集排序。ORDER BY 可以在查询结束时出现一次。例如，
-
UNION 和 INTERSECT 运算符是可交换的，即查询的顺序并不重要；它不会改变最终结果。
-
在性能方面，UNION ALL 与 UNION 相比表现出更好的性能，因为在过滤重复项和对结果集进行排序时不会浪费资源。
-
集合运算符可以是子查询的一部分。
-
不能在包含 TABLE 集合表达式的 SELECT 语句中使用集合运算符。
-
LONG、BLOB、CLOB、BFILE、VARRAY 或嵌套表不允许在 Set 运算符中使用。For update 子句不允许与 set 运算符一起使用。
联盟
当使用 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 和 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 运算符，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
减
减号运算符显示第一个查询中存在但第二个查询中不存在的行，默认情况下没有重复和数据按升序排列。
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 (number) 的数据类型不匹配。因此，由于兼容性问题，执行以下查询会引发错误。
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 查询中的列。因此，建议使用列位置对复合查询进行排序。
下面的复合查询将两个部门的结果统一起来，并按 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;
