Oracle 集合运算符

1.集合运算符

集合运算符查询组合两个组件查询的结果到一个结果，包含集合运算符的查询称为复合查询。SQL集合运算符如下：

集合运算符

OperatorReturns

UNION

All distinct rows selected by either query

UNION ALL

All rows selected by either query, including all duplicates

INTERSECT

All distinct rows selected by both queries

MINUS

All distinct rows selected by the first query but not the second

 

你可以使用集合运算符UNION，UNION ALL，INTERSECT和MINUS组合多个查询。所有集合运算符具有相同的优先级。如果一个SQL语句包含多个集合运算符，那么Oracle数据库按照从左到有解析他们，除非使用括号显示指定另外的顺序。

在复合查询的查询组件的查询列表中的对应表达式数量必须相匹配，且必须是相同数据类型组（如数字和字符）。

如果查询组件查询字符数据，则返回值的数据类型被以下确定：

• 如果这两个查询的查询列是相等长度CHAR数据类型的值，则返回值具有该长度的CHAR数据类型。如果查询列是不同长度的CHAR值，则返回值是VARCHAR2具有较大CHAR值的长度。
• 如果一个或则两个查询的查询列是VARCHAR2，则返回值的数据类型为VARCHAR2

如果查询组件查询数值数据，则返回值的数据类型由数字的优先级确定：

• 如果查询有BINARY_DOUBLE类型的查询值，则返回值的数据类型为BINARY_DOUBLE。
• 如果查询没有BINARY_DOUBLEl类型的查询值，但是查询有BINARY_FLOAT类型的值，则返回值的数据类型为BINARY_FLOAT。
• 如果所有查询的查询值为NUMBER类型，则返回值的数据类型为NUMBER。

在使用集合运算符的查询中，Oracle不会跨越数据类型组执行隐式转换。因此，如果查询组件对应的表达解析为数字类型和字符类型，Oracle会返回一个错误。

 

2.集合运算符限制

集合运算符受以下限制:

• 集合运算符对BLOB，CLOB，BFILEVARRAY，或者嵌套表类型列无效。
•  UNION,INTERSECT和MINUS运算符对LONG类型列无效。
• 如果集合运算符之前的查询列表中包含一个表达式，则为了引用order_by_clause子句必须为该表达式提供别名。
• 集合运算符不能指定 for_update_clause 子句。
• 不能在集合运算符的子查询中使用order_by_clause子句。
• 在SELECT语句包含TABLE集合表达式中使用这些集合运算符。

3.Examples

3.1 UNION

select e.deptno
  from emp e
union
select d.deptno from dept d;

 

3.2 UNION ALL

select e.deptno
  from emp e
union all
select d.deptno from dept d;

 

3.3 INTERSECT

select e.deptno
  from emp e
intersect
select d.deptno from dept d;

 

3.4 MINUS

select d.deptno
  from dept d
minus
select e.deptno from emp e;

 

3.5复合查询隐式转换

select to_binary_float(3)
  from dual
intersect
select 3f from dual;

select 3
  from dual
intersect
select 3f from dual;

 

3.6跨数据类型组转换错误

Oracle不能跨数据类型组转换，因此该查询是无效的

select '3'
  from dual
intersect
select 3f from dual