Oracle 集合运算(UNION、UNION ALL、INTERSECT、MINUS)

最新推荐文章于 2021-12-15 10:51:11 发布

cuixu4010

最新推荐文章于 2021-12-15 10:51:11 发布

阅读量452

点赞数

文章标签：数据库

集合运算组合两个或多个部分查询的结果到一个结果中。包含集合运算的查询称为复合查询。

Operator Returns

UNION(联合) 由每个查询选择的所有不同的行(无重复值)

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

INTERSECT(交叉) 由两个查询选择的所有不同的行

MINUS 由第一个查询选择的所有不同的行

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

INTERSECT (相交) 和MINUS (相减) 运算不是ANSI SQL-99兼容的，他们是Oracle特定的。

■联合(UNION)

UNION(联合)运算

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

?原则

:: 被选择的列数和列的数据类型必须是与所有用在查询中的SELECT语句一致。列的名字不必相同。

:: 联合运算在所有被选择的列上进行。

:: 在做重复检查的时候不忽略空(NULL)值。

:: IN运算有比UNION运算高的优先级。

:: 在默认情况下，输出以SELECT子句的第一列的升序排序。

■全联合(UNION ALL)

运算从两个查询返回包括所有重复值的结果

原则

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

:: 不能使用DISTINCT关键字。

注：除了上面的两点，UNION ALL的原则与UNION相同。

■相交(INTERSECT)

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

原则

:: 在查询中被 SELECT 语句选择的列数和数据类型必须与在查询中所使用的所有的 SELTCT 语句中的一样，但列的名字不必一样。

:: 颠倒相交的表的排序不改变结果。

:: 相交不忽略空值。

拓展：
ocp11g 051中有道关于intersect的考题（第9题）

9. Which statement is true regarding the INTERSECT operator?

A. It ignores NULL values.

B. Reversing the order of the intersected tables alters the result.

C. The names of columns in all SELECT statements must be identical.

D. The number of columns and data types must be identical for all SELECT statements in the query.

ANSWER：D

■相减(MINUS)

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

原则

:: 在查询中被SELECT语句选择的列数和数据类型必须与在查询中所使用的所有的SELTCT语句中的一样，但列的名字不必一样。

:: 对于MINUS运算，在WHERE子句中所有的列都必须在SELECT子句中。

■

※集合运算的原则

:: 在两个SELECT列表中的表达式必须在数目上和数据类型上相匹配

:: 可以用圆括号改变执行的顺序

:: ORDER BY子句：

–只能出现在语句的最后

–从第一个SELECT语句接收列名、别名，或者位置记号

:: 集合运算可以用在子查询中。

■Oracle 服务器和集合运算

:: 除了UNION ALL，重复行自动被清除

:: 在结果中的列名是第一个查询中出现的列名

:: 除了UNION ALL(无排序)，默认情况下按升序顺序输出

在一个复合查询的各查询组成部分的选择列表中相应的表达式必须在数目和类型上匹配。如果查询的组成部分选择字符数据，返回值的数据类型被如下决定：

:: 如果查询选择的数据类型的值为CHAR，那么，返回值的数据类型也为CHAR。

:: 如果查询选择的两者之一或两者的数据类型值为VARCHAR2，那么，返回值的数据类型也是VARCHAR2。

你可能需要提及，输出以第一个SELECT子句的第一列的升序被排序，然后是第二列，等等。

■匹配SELECT语句 //凑数

eg:

SELECT department_id, TO_NUMBER(null) location, hire_date

FROM employees

UNION

SELECT department_id, location_id, TO_DATE(null)

FROM departments;

DEPARTMENT_ID LOCATION HIRE_DATE

------------- ---------- ----------

80 21-4月 -00

90 1700

90 17-6月 -87

90 21-9月 -89

90 13-1月 -93

100 1700

100 16-8月 -94

100 17-8月 -94

100 28-9月 -97

100 30-9月 -97

100 07-3月 -98

匹配SELECT语句

由于在两个查询的SELECT列表中的表达式必须在数量上匹配，你可以使用虚拟列和转换函数数据类型来满足该规则。在幻灯片中使用了虚拟列，在第一个查询中的TO_NUMBER函数被用以匹配第二个查询中返回的LOCATION_ID列的数字数据类型，同样地，第二个查询中的TO_DATE函数被用于匹配第一个查询返回的日期数据类型。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27064835/viewspace-1768003/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27064835/viewspace-1768003/