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

最新推荐文章于 2024-09-08 11:46:44 发布

转载最新推荐文章于 2024-09-08 11:46:44 发布 · 4.3k 阅读

文章标签：

#union #union all #insersect #minus

sql 专栏收录该内容

36 篇文章

订阅专栏

本文深入解析SQL中的四种集合运算：UNION、UNIONALL、INTERSECT和MINUS，详细解释它们的用法、规则和应用场景。通过实例展示如何在SQL查询中使用这些运算符，以及如何优化查询性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

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 语句中的一样，但列的名字不必一样。

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

:: 相交不忽略空值。

■相减(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函数被用于匹配第一个查询返回的日期数据类型。

Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All，对两个结果集进行并集操作，包括重复行，不进行排序；

Intersect，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

Minus，对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

order by子句必须写在最后一个结果集里，并且其排序规则将改变操作后的排序结果；并且对于Union、Union All、Intersect、Minus都有效。

表头会用第一个连接块的字段。

1.Union

union用法中,两个select语句的字段类型匹配,而且字段个数要相同.

UNION连接的两个表是同一表，使用UNION会过滤重复行，显示的是一张表的数据。

下面SQL查询语句中两个表完全一样，所以查询显示的是单张表的信息。

SCOTT@bys1>select deptno,dname as "aa",loc from dept union select deptno,dname,loc from dept;
    DEPTNO aa             LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

2.Union All

UNION ALL,它的用法和union一样,只不过union含有distinct的功能,它会把两张表了重复的记录去掉,

而union all不会,所以从效率上,union all 会高一点,但在实际中用到的并不是很多.

效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL。

尽量使用union all，因为union需要进行排序，去除重复记录，效率低。

下面SQL查询语句中两个表完全一样，所以查询显示了两次表内信息。

SCOTT@bys1>select deptno,dname,loc as "aa" from dept union allselect deptno,dname,loc as "bb" from dept;
    DEPTNO DNAME          aa
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
3.Intersect

对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；返回两个表共同含有的数据，取出交集作为最终的返回结果。

下面SQL查询语句中两个表完全一样，所以查询显示的是单张表的信息。

SCOTT@bys1>select deptno,dname,loc as "aa" from dept intersect select deptno,dname,loc as "bb" from dept;
    DEPTNO DNAME          aa
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
4.Minus

对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

第一个结果集减去第二个结果集中的内容，所剩余的内容作为最终的返回结果。

下面SQL查询语句中两个表完全一样，所以查询显示无信息。

SCOTT@bys1>select deptno,dname,loc as "aa" from dept minus select deptno,dname,loc as "bb" from dept;

no rows selected

转：http://blog.chinaunix.net/uid-17277885-id-2809621.html

转：http://www.2cto.com/database/201311/254818.html