Sql Certificate 简明教程

SQL - Using the Set Operators

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

Set operators are used to join the results of two (or more) SELECT statements.The SET operators available in Oracle 11g are UNION,UNION ALL,INTERSECT,and MINUS.

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

The UNION set operator returns the combined results of the two SELECT statements.Essentially,it removes duplicates from the results i.e. only one row will be listed for each duplicated result.To counter this behavior,use the UNION ALL set operator which retains the duplicates in the final result.INTERSECT lists only records that are common to both the SELECT queries; the MINUS set operator removes the second query’s results from the output if they are also found in the first query’s results. INTERSECT and MINUS set operations produce unduplicated results.

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

All the SET operators share the same degree of precedence among them.Instead,during query execution, Oracle starts evaluation from left to right or from top to bottom.If explicitly parentheses are used, then the order may differ as parentheses would be given priority over dangling operators.

Points to remember -

  1. Same number of columns must be selected by all participating SELECT statements.Column names used in the display are taken from the first query.

  2. Data types of the column list must be compatible/implicitly convertible by oracle. Oracle will not perform implicit type conversion if corresponding columns in the component queries belong to different data type groups.For example, if a column in the first component query is of data type DATE, and the corresponding column in the second component query is of data type CHAR,Oracle will not perform implicit conversion, but raise ORA-01790 error.

  3. Positional ordering must be used to sort the result set. Individual result set ordering is not allowed with Set operators. ORDER BY can appear once at the end of the query. For example,

  4. UNION and INTERSECT operators are commutative, i.e. the order of queries is not important; it doesn’t change the final result.

  5. Performance wise, UNION ALL shows better performance as compared to UNION because resources are not wasted in filtering duplicates and sorting the result set.

  6. Set operators can be the part of sub queries.

  7. Set operators can’t be used in SELECT statements containing TABLE collection expressions.

  8. The LONG, BLOB, CLOB, BFILE, VARRAY,or nested table are not permitted for use in Set operators.For update clause is not allowed with the set operators.

UNION

通过 UNION 运算符联接多个 SELECT 查询时，Oracle 将显示所有复合 SELECT 查询的合并结果，在不忽略 NULL 值的情况下，删除所有重复项并按排序顺序（默认情况下为升序）显示。

When multiple SELECT queries are joined using UNION operator, Oracle displays the combined result from all the compounded SELECT queries,after removing all duplicates and in sorted order (ascending by default), without ignoring the NULL values.

考虑使用 UNION 运算符联接的以下五个查询。最终的合并结果集包含来自所有 SQL 的值。请注意，数据会重复删除并进行排序。

Consider the below five queries joined using UNION operator.The final combined result set contains value from all the SQLs. Note the duplication removal and sorting of data.

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 会抛出错误消息。

To be noted, the columns selected in the SELECT queries must be of compatible data type. Oracle throws an error message when the rule is violated.

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 and UNION ALL are similar in their functioning with a slight difference. But UNION ALL gives the result set without removing duplication and sorting the data. For example,in above query UNION is replaced by UNION ALL to see the effect.

考虑 UNION 部分中演示的查询。请注意输出中的差异，该差异是在没有排序和去重的情况下生成的。

Consider the query demonstrated in UNION section. Note the difference in the output which is generated without sorting and deduplication.

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 语句中的公有行，并按照排序顺序（默认情况下为升序）对数据进行排列，且不会重复。

Using INTERSECT operator, Oracle displays the common rows from both the SELECT statements, with no duplicates and data arranged in sorted order (ascending by default).

例如，下面的 SELECT 查询获取在部门 10 和 20 中常见的工资。根据 ISO SQL 标准，INTERSECT 在集运算符评估优先级中高于其他运算符，但这尚未被 Oracle 采纳。

For example,the below SELECT query retrieves the salary which are common in department 10 and 20.As per ISO SQL Standards, INTERSECT is above others in precedence of evaluation of set operators but this is not still incorporated by Oracle.

SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 10
INTRESECT
SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 20

SALARY
---------
1500
1200
2000

MINUS

Minus 运算符显示仅存在于第一个查询中但不存于第二个查询中的行，并且不会重复，数据按照升序（默认情况下为升序）进行排列。

Minus operator displays the rows which are present in the first query but absent in the second query, with no duplicates and data arranged in ascending order by default.

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 以匹配查询中所选列的类型。

There may be the scenarios where the compound SELECT statements may have different count and data type of selected columns. Therefore, to match the column list explicitly, NULL columns are inserted at the missing positions so as match the count and data type of selected columns in each SELECT statement. For number columns, zero can also be substituted to match the type of the columns selected in the query.

在下面的查询中，员工姓名（varchar2）和位置 ID（number）的数据类型不匹配。因此，执行下面的查询将由于兼容性问题而引发错误。

In the below query, the data type of employee name (varchar2) and location id (number) do not match. Therefore, execution of the below query would raise error due to compatibility issue.

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 和员工名称来匹配列。

Explicitly, columns can be matched by substituting NULL for location id and Employee name.

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 查询中的列。因此，建议使用列位置对复合查询进行排序。

The ORDER BY clause can appear only once at the end of the query containing compound SELECT statements.It implies that individual SELECT statements cannot have ORDER BY clause. Additionally, the sorting can be based on the columns which appear in the first SELECT query only. For this reason, it is recommended to sort the compound query using column positions.

下面的复合查询汇集了两个部门的结果，并按 SALARY 列进行排序。

The compund query below unifies the results from two departments and sorts by the SALARY column.

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;