[转贴]Oracle集合的用法

转自ITPUB(DingJun):

集合操作

简表：

Operator

Returns

Content

UNION

由每个查询选择的所有不重复的行

并集不包含重复值，默认按第1个查询的第1列升序排列

UNION ALL

由每个查询选择的所有的行，包括所有重复的行

包括所有重复的行 完全并集包含重复值，不排序

INTERSECT

由每个查询选择的所有不重复的相交行

交集，不包含重复行，按第1个查询的第1列升序排列

MINUS

在第一个查询中，不在后面查询中的行

不包含重复行，按第1个查询的第1列升序排列

UNION和UNION ALL

UNION(联合)运算：

UNION运算返回所有由任一查询选择的行。用UNION运算从多表返回所有行，但除去任何重复的行。

原则：

• 被选择的列数和列的数据类型必须是与所有用在查询中的SELECT语句一致。列的名字不必相同。
• 联合运算在所有被选择的列上进行。
• 在做重复检查的时候不忽略空(NULL)值。(如果有重复空行，则保留一个空行)
• IN运算有比UNION运算高的优先级。
• 在默认情况下，输出以SELECT子句的第一列的升序排序。

 

全联合(UNION ALL)运算：

用全联合运算从多个查询中返回所有行。

原则：

• 和联合不同，重复的行不被过滤，并且默认情况下输出不排序。

 

Union all效率一般比union高。Union all内部不做排序工作，也不做剔除重复行工作，而union则做这个工作。

 

使用：

Select statementunion | union all Select statement…..;

intersect交集操作

用相交运算返回多个查询中所有的公共行。 无重复行。

原则：

• 在查询中被 SELECT语句选择的列数和数据类型必须与在查询中所使用的所有的SELETCT语句中的一样，但列的名字不必一样。
• 相交的表的倒序排序不改变结果。
• 相交不忽略null值。

使用：

Select statementintersect Select statement….;

 

minus差集操作

相减运算，求差集。无重复行。

用相减运算返回由第一个查询返回的行，那些行不出现在第二个查询中 (第一SELECT语句减第二个SELECT语句)。

原则：

• 在查询中被SELECT语句选择的列数和数据类型必须与在查询中所使用的所有的SELTCT语句中的一样，但列的名字不必一样。
• 对于MINUS运算，在WHERE子句中所有的列都必须在SELECT子句中。

使用：

Select statement minusSelect statement….;

 

集合运算的原则

• 在两个SELECT 列表中的表达式必须在数目上和数据类型上相匹配
• 可以用圆括号改变执行的顺序
• ORDER BY子句：
  • 只能出现在语句的最后
  • 从第一个SELECT语句接收列名、别名，或者位置记号.查询结果的列名与第一个SELECT 语句保持一致。
•  可以用于子查询。
• 除了UNION ALL，重复行自动被清除
• 在结果中的列名是第一个查询中出现的列名
• 除了UNION ALL，默认情况下按升序顺序输出。除union all外，其他的都做排序工作，而且都剔除重复结果。

 

总结

所有的集合运算与等号的优先级相同，如果SQL语句包含多个集合运算并且没有圆括号明确地指定另一个顺序，Oracle服务器将以从左到右的顺序计算。你应该使用圆括号来明确地指定带另外的集合运算的INTERSECT (相交)运算查询中的赋值顺序。

集合操作是对多个查询结果进行并集，差集，交集的操作，都不忽略null行。

除了union all 默认不做排序和剔除重复行的操作外，union,minus,intersect都默认按第1个查询结果的第1列进行升序排列，并且不包含重复行。对集合运算的结果要排序，只能在最后一个select 后面排序，是对整个集合操作进行排序，而不是对最后一个select 进行排序，排序选择的列必须是第1个select 中出现的列，如果有别名，则使用别名，不能选用没有查询出来的列(这和普通的order by不一样，普通的order by可以选用不在select中的列排序)，当然可以按位置指定order by，比如order by 1 desc,就是按第1列降序。在业务要求允许的情况下，能使用union all就不要使用union，因为unionall不做排序剔重操作，使用union all效率方面要好点。