Sql Certificate 简明教程

SQL - Using the Set Operators

集合运算符用于连接两个（或更多）SELECT 语句的结果。Oracle 11g 中提供的集合运算符包括 UNION、UNION ALL、INTERSECT 和 MINUS。

UNION 集合运算符返回两个 SELECT 语句的组合结果。实质上，它会删除结果中的重复项，即每个重复结果仅会列出一次。为了解决这种行为，请使用 UNION ALL 集合运算符，它会在最终结果中保留重复项。INTERSECT 仅列出 SELECT 查询中都存在的记录；MINUS 集合运算符从输出中删除第二个查询的结果（如果它们也出现在第一个查询的结果中）。INTERSECT 和 MINUS 集合运算会产生不重复的结果。

所有集合运算符在它们之间共享相同级别的优先级。相反，在查询执行期间，Oracle 从左向右或从上到下开始求值。如果明确使用了括号，则顺序可能有所不同，因为括号比悬空运算符具有更高优先级。

Points to remember -

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

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

  3. 必须使用位置顺序来对结果集进行排序。集合运算符不允许使用单独结果集排序。ORDER BY 只能出现在查询末尾一次。例如，

  4. UNION 和 INTERSECT 运算符是交换的，即查询的顺序不重要；它不会更改最终结果。

  5. 在性能方面，与 UNION 相比，UNION ALL 具有更好的性能，因为不会浪费资源来筛选重复项和对结果集进行排序。

  6. 集运算符可能是子查询的一部分。

  7. 集运算符不能用于包含 TABLE 集合表达式的 SELECT 语句。

  8. 集运算符中不允许使用 LONG、BLOB、CLOB、BFILE、VARRAY 或嵌套表，集运算符不允许使用 For update 子句。

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

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

Matching the SELECT statement

在某些情况下，复合 SELECT 语句可能具有不同的计数和所选列的数据类型。因此，为了显式匹配列列表，在缺失的位置插入 NULL 列，以便匹配每条 SELECT 语句中所选列的计数和数据类型。对于数字列，还可以替换 0 以匹配查询中所选列的类型。

在下面的查询中，员工姓名（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;

Using ORDER BY clause in SET operations

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;