SQL - 使用集合运算符

上一页
下一页

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

UNION 集合运算符返回两个 SELECT 语句的组合结果。从本质上讲，它会从结果中删除重复项，即每个重复结果只列出一行。要解决此问题，请使用 UNION ALL 集合运算符，它保留最终结果中的重复项。INTERSECT 仅列出两个 SELECT 查询中共同存在的记录；MINUS 集合运算符从输出中删除第二个查询的结果，如果这些结果也存在于第一个查询的结果中。INTERSECT 和 MINUS 集合运算产生不重复的结果。

所有集合运算符之间具有相同的优先级。相反，在查询执行期间，Oracle 从左到右或从上到下开始评估。如果显式使用括号，则顺序可能不同，因为括号将优先于悬挂运算符。

要点 -

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

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

  • 必须使用位置排序来排序结果集。使用集合运算符不允许对单个结果集进行排序。ORDER BY 可以出现在查询的末尾。例如，

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

  • 从性能方面来看，UNION ALL 比 UNION 表现更好，因为不会浪费资源来过滤重复项和排序结果集。

  • 集合运算符可以是子查询的一部分。

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

  • LONG、BLOB、CLOB、BFILE、VARRAY 或嵌套表不允许在集合运算符中使用。更新子句不允许与集合运算符一起使用。

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

匹配 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;

在集合运算中使用 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;
打印页面
上一页
下一页
广告