集合运算

一、Oracle支持４种集合运算符:UNION、UNION ALL、INTERSECT及MINUS。集合运算符将两个或更多SELECT语句的结果合并成一个结果集。其与连接的区别就在于连接是用来将不同表中的列组合起来形成一行。

 

二、所有进行集合运算的查询都必须符合下面的条件：
１、所有的输入查询必须返回相同数目的列。
２、每一列的数据类型必须与对应的其他输入查询一致。（数据类型可以不直接匹配，但必须可以隐式转化）
３、ORDER BY子句不能在某个单独的查询中应用，只能用在整个查询的最后，用来对整个集合运算的结果集进行排序。
４、列名源自第一个输入查询。（UNION和INTERSECT运算可以互换的，但使用MINUS的时候，顺序就很重要了）

 

三、集合运算的实验
１、UNION/UNION ALL
UNION
　返回来自所有输入查询的不包含重复的结果集。
UNION ALL
　返回两个结果集的所有行,包含重复。除了UNION ALL以外的所有集合运算都需要对结果进行排序/取唯一值操作，这就意哨声着需要额外的支出来处理查询。如果你知道不会出现重复，或者你并不关心是否会出现重复，请一定使用UNION ALL。如下图可以很直观地展现UNION/UNION ALL运算的结果集是如何得到的。

 

 

（１）建表test1

> CREATE TABLE test1 (
  2  id INTEGER NOT NULL PRIMARY KEY,
  3  color VARCHAR(10) NOT NULL);

Table created.

（２）建表test2

> CREATE TABLE test2 (
  2  id INTEGER NOT NULL PRIMARY KEY,
  3  color VARCHAR(10) NOT NULL);

Table created.

（３）建表test3

> CREATE TABLE test3 (
  2  color VARCHAR(10) NOT NULL);
INSERT INTO test1 VALUES (1, 'RED');
INSERT INTO test1 VALUES (2, 'RED');

Table created.

（４）插入数据

> INSERT INTO test1 VALUES (3, 'ORANGE');
INSERT INTO test1 VALUES (4, 'ORANGE');
INSERT INTO test1 VALUES (5, 'ORANGE');

1 row created.

> 
1 row created.

> INSERT INTO test1 VALUES (6, 'YELLOW');

1 row created.

> 
1 row created.

> 
1 row created.

> 
1 row created.

> INSERT INTO test1 VALUES (7, 'GREEN');

1 row created.

> INSERT INTO test1 VALUES (8, 'BLUE');

1 row created.

> INSERT INTO test1 VALUES (9, 'BLUE');

1 row created.

> INSERT INTO test1 VALUES (10, 'VIOLET');

1 row created.

> INSERT INTO test2 VALUES (1, 'RED');
INSERT INTO test2 VALUES (2, 'RED');

1 row created.

> 
1 row created.

> INSERT INTO test2 VALUES (3, 'BLUE');

1 row created.

> INSERT INTO test2 VALUES (4, 'BLUE');

1 row created.

> INSERT INTO test2 VALUES (5, 'BLUE');

1 row created.

> INSERT INTO test2 VALUES (6, 'GREEN');

1 row created.

> commit;

Commit complete.

（５）查test1表的数据有10条

> select color from test1;

COLOR
----------
RED
RED
ORANGE
ORANGE
ORANGE
YELLOW
GREEN
BLUE
BLUE
VIOLET

10 rows selected.

（６）查test2表的数据有6条

> select * from test2;

        ID COLOR
---------- ----------
         1 RED
         2 RED
         3 BLUE
         4 BLUE
         5 BLUE
         6 GREEN

6 rows selected.

（７）查test3表的数据无记录

> select color from test3;

no rows selected

--表test1与表test2进行UNION操作
> select color from test1
  2  union
  3  select color from test2;

COLOR
----------
BLUE
GREEN
ORANGE
RED
VIOLET
YELLOW

6 rows selected.

（８）表test1与表test2进行UNION　ALL操作

> select color from test1
  2  union all
  3  select color from test2;

COLOR
----------
RED
RED
ORANGE
ORANGE
ORANGE
YELLOW
GREEN
BLUE
BLUE
VIOLET
RED
RED
BLUE
BLUE
BLUE
GREEN

16 rows selected.

（９）表test1与表test3进行UNION操作

> select color from test1
  2  union
  3  select color from test3;

COLOR
----------
BLUE
GREEN
ORANGE
RED
VIOLET
YELLOW

6 rows selected.

（１０）表test1与表test2进行UNION操作

> select * from test1
  2  union
  3  select color from test2;
select * from test1
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result columns

 

２、INTERSECT
INTERSECT用来返回在所有输入查询中都存在的唯一行集.通常用来代替exists(半连接)查询。所解决的问题可以描述为“我需要返回源A和Ｂ中都存在的数据行集”。如下图可以很直观的展示了INTERSECT运算的结果集是如何生成的。

　　　　　　　　　　　　　　　　　

 

（１）表test1与test2进行INTERSECT操作

> select color from test1
  2  intersect
  3  select color from test2;

COLOR
----------
BLUE
GREEN
RED

（２）表test1与test３进行INTERSECT操作

> select color from test1
  2  intersect
  3  select color from test3;

no rows selected

３、MINUS
当第一个输入查询的结果作为基础数据集减去另一个输入查询结果作为最终结果集的时候使用MINUS。MINUS通常用来替代not exists(连接结)查询。所解决的问题可以描述为“我需要返回在数据行源A中存在但是在B中不存在的数据行集”。如下图可以很直观地展现MINUS运算的结果集是如何得到的。

　　　　　　　　　　　　　　　　

 

（１）表test1和表test2进行MINUS操作

> select color from test1
  2  minus
  3  select color from test2;

COLOR
----------
ORANGE
VIOLET
YELLOW

（２）用not exists来替代MINUS的操作，结果与上面MINUS操作的结果一样

> select distinct color from test1
  2  where not exists (select null from test2 where test2.color = test1.color) ;

COLOR
----------
ORANGE
VIOLET
YELLOW

（３）表test2和表test1进行MINUS操作

> select color from test2
  2  minus
  3  select color from test1;

no rows selected

（４）表test1和表test3进行MINUS操作

> select color from test1
  2  minus
  3  select color from test3;

COLOR
----------
BLUE
GREEN
ORANGE
RED
VIOLET
YELLOW

6 rows selected.

**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
QQ: 252803295
Email:[e-mail]
尖峰官网：
尖峰淘宝：
WEIBO：
尖峰OCP认证考试群297227448  
尖峰OCM认证考试群99606943
尖峰MySQL研究院群314746420
尖峰JAVA研究院群 315405063  
尖峰Hadoop研究院群366294602
尖峰线上技术分享群252296815
尖峰SQL优化研究院群250057366