Oracle之SQL基础--合并查询

 

在Oracle sql查询过程当中，可以使用集合运算符UNION,UNION ALL,INTERSECT,MINUS将多个查询的结果集合并

 

语法：select  query_statement1 [UNION|UNION ALL|INTERSECT|MINUS] select query_statement2;

 

这里呢？其实和数学里面的集合是可以等同理解的，所以它也有一些条件限制：

 

1，当要合并几个查询结果集的时候，这几个查询结果集必须要有相同的列数与数据类型。

 

2，如果要对最终的结果集排序，则只能在最后一个查询用order by子句指明排序列

这里呢，个人建表并参考和实验总结了一些做法，希望能对自己和大家的学习都有所帮助。

 

1,Union(取几个集合并集)
create table fangfan_test_emp01 as select * from emp;
create table fangfan_test_emp02 as select * from emp;
delete from fangfan_test_emp01 where deptno=10;
delete from fangfan_test_emp02 where deptno=20;
select distinct deptno from fangfan_test_emp01;
select distinct deptno from fangfan_test_emp02;
select distinct deptno from fangfan_test_emp01 union select distinct deptno from fangfan_test_emp02 order by deptno desc;

2,Union ALL (A+B)重复数据会存在两份
select deptno from fangfan_test_emp01 union all select deptno from fangfan_test_emp02 order by deptno desc;
select distinct deptno from fangfan_test_emp01 union all select distinct deptno from fangfan_test_emp02 order by deptno desc;

3,Intersect (取几个集合的交集)返回同时存在于几个查询结果集中的记录。
select deptno from fangfan_test_emp01 intersect select deptno from fangfan_test_emp02 order by deptno desc;

4,MINUS(在第一个结果集当中存在，而在第二个结果集当中不存在的记录)
select distinct deptno from fangfan_test_emp01 minus select distinct deptno from fangfan_test_emp02 order by deptno desc;

5,有关集合的运算
比如我希望得到除去集合交集的其余的部分
取两张表的并集union_fang_emp
with union_fang_emp as (select deptno from fangfan_test_emp01 union select deptno from fangfan_test_emp02 order by deptno)
取两张表的交集intersect_fang_emp
with intersect_fang_emp as (select deptno from fangfan_test_emp01 intersect select deptno from fangfan_test_emp02 order by deptno)
请注意在应用到语句当中的时候是没有分号的。
现在用并集减去交集就可以了
请注意用with语句的时候没有分号
第一种：
with union_fang_emp as (select deptno from fangfan_test_emp01 union select deptno from fangfan_test_emp02 order by deptno),
intersect_fang_emp as (select deptno from fangfan_test_emp01 intersect select deptno from fangfan_test_emp02 order by deptno)
/*not exists*/
select union_fang_emp.deptno from union_fang_emp where not exists (select deptno from intersect_fang_emp where deptno=union_fang_emp.deptno);

第二种：
with union_fang_emp as (select deptno from fangfan_test_emp01 union select deptno from fangfan_test_emp02 order by deptno),
intersect_fang_emp as (select deptno from fangfan_test_emp01 intersect select deptno from fangfan_test_emp02 order by deptno)
select * from union_fang_emp minus select * from intersect_fang_emp;/*right*/
select * from (union_fang_emp minus intersect_fang_emp);/*wrong*/
select * from union_intersect_fang_emp;/*right*/
union_intersect_fang_emp as (union_fang_emp minus intersect_fang_emp)/*直接加在with之后不可用*/

最完整的用法如下所示：
with union_fang_emp as (select deptno from fangfan_test_emp01 union select deptno from fangfan_test_emp02 order by deptno),
intersect_fang_emp as (select deptno from fangfan_test_emp01 intersect select deptno from fangfan_test_emp02 order by deptno),union_intersect_fang_emp as (select * from union_fang_emp minus select * from intersect_fang_emp)
select * from union_intersect_fang_emp;

这里要查询的话只能使用*,不能用deptno
select deptno from union_fang_emp,intersect_fang_emp;
select * from union_fang_emp,intersect_fang_emp;