使用集合运算符

使用集合运算符

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;

觉得文章有用？

点个广告表达一下你的爱意吧 ！😁