Oracle中Union与Union All的区别(适用多个数据库)

Oracle中Union与Union All的区别(适用多个数据库)

如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来

 

Union 与 Union ALL 的作用都是合并 SELECT 的查询结果集，那么它们有什么不同呢？

Union 将查询到的结果集合并后进行重查，将其中相同的行去除。缺点：效率低；
而Union ALL 则只是合并查询的结果集，并不重新查询，效率高，但是可能会出现冗余数据。

 

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

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

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

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

可以在最后一个结果集中指定Order by子句改变排序方式。

 

例如：

复制代码 代码如下:

select employee_id,job_id from employees
union
select employee_id,job_id from job_history

以上将两个表的结果联合在一起。这两个例子会将两个select语句的结果中的重复值进行压缩，也就是结果的数据并不是两条结果的条数的和。如果希望即使重复的结果显示出来可以使用union all,例如：

 

2.在oracle的scott用户中有表emp

复制代码 代码如下:

select * from emp where deptno >= 20
union all
select * from emp where deptno <= 30

这里的结果就有很多重复值了。

有关union和union all关键字需要注意的问题是：

 

union 和 union all都可以将多个结果集合并，而不仅仅是两个，你可以将多个结果集串起来。
使用union和union all必须保证各个select 集合的结果有相同个数的列，并且每个列的类型是一样的。但列名则不一定需要相同，oracle会将第一个结果的列名作为结果集的列名。例如下面是一个例子：

 

代码如下:
select empno,ename from emp
union
select deptno,dname from dept

 

我们没有必要在每一个select结果集中使用order by子句来进行排序，我们可以在最后使用一条order by来对整个结果进行排序。例如：

复制代码 代码如下:

select empno,ename from emp
union
select deptno,dname from dept
order by ename;

 

转自：http://www.jb51.net/article/30792.htm

 

本人写的，比较复杂一点的sql（left join  union all）

 

-- 查询基本信息 
select * from 
( 
select 
max(trans_item.BASIC_TERM_ID) BASIC_TERM_ID, 
trans_item.APP_TERM_NO APP_TERM_NO, 
max(trans_item.DEVICE_TYPE) DEVICE_TYPE, 
max(trans_item.MODEL_DESC) MODEL_DESC, 
max(trans_item.branch_name) branch_name, 
max(trans_item.sub_name) sub_name, 
max(trans_item.self_name) self_name, 
max(trans_item.INST_TYPE) INST_TYPE 
from( 
select    term.TERMINAL_ID BASIC_TERM_ID, 
term.APP_TERM_NO APP_TERM_NO, 
device.DEVICE_TYPE DEVICE_TYPE, 
model.MODEL_DESC MODEL_DESC, 
branch.SHORT_NAME branch_name, 
subbranch.SHORT_NAME sub_name, 
self.SHORT_NAME self_name, 
self.INST_TYPE INST_TYPE 
from SELFCUR.OPS_TERMINAL_INFO    term, 
SELFCUR.OPS_DEVICE_INFO     device, 
SELFCUR.OPS_DEVICE_MODEL    model, 
SELFCUR.OPS_INSTITUTION     branch, 
SELFCUR.OPS_INSTITUTION     subbranch, 
SELFCUR.OPS_INSTITUTION     self, 
SELFCUR.BIZ_MAIN_TRANS    trans 
where    trans.TERM_ID=term.APP_TERM_NO 
and term.TERMINAL_ID=device.TERMINAL_ID 
and device.MODEL_ID=model.MODEL_ID 
and term.INST_ID=self.INST_ID 
and self.PARENT_INST_ID=subbranch.INST_ID 
and subbranch.PARENT_INST_ID=branch.INST_ID 
-- 这里需要加入特殊符号来标识，用界面传过来的参数组成sql进行替换 
--XXXXYYYY-- 
union all 
select    term.TERMINAL_ID BASIC_TERM_ID, 
term.APP_TERM_NO APP_TERM_NO, 
device.DEVICE_TYPE DEVICE_TYPE, 
model.MODEL_DESC MODEL_DESC, 
branch.SHORT_NAME branch_name, 
subbranch.SHORT_NAME sub_name, 
self.SHORT_NAME self_name, 
self.INST_TYPE INST_TYPE 
from SELFCUR.OPS_TERMINAL_INFO    term, 
SELFCUR.OPS_DEVICE_INFO     device, 
SELFCUR.OPS_DEVICE_MODEL    model, 
SELFCUR.OPS_INSTITUTION     branch, 
SELFCUR.OPS_INSTITUTION     subbranch, 
SELFCUR.OPS_INSTITUTION     self, 
SELFCUR.BIZ_MAIN_TRANS_HIS    trans_his 
where    trans_his.TERM_ID=term.APP_TERM_NO 
and term.TERMINAL_ID=device.TERMINAL_ID 
and device.MODEL_ID=model.MODEL_ID 
and term.INST_ID=self.INST_ID 
and self.PARENT_INST_ID=subbranch.INST_ID 
and subbranch.PARENT_INST_ID=branch.INST_ID 
-- 这里需要加入特殊符号来标识，用界面传过来的参数组成sql进行替换 
--XXXXYYYY-- 
)trans_item 
group by trans_item.APP_TERM_NO 
)trans_basic 
left join( 
-- 联通缴费 
select 
trans_pay_lt.TERM_ID TERM_ID, 
count(1) PAY_LT_Count, 
sum(trans_pay_lt.TRAN_AMT) PAY_LT_Money 
from( 
select trans.TERM_ID TERM_ID, 
trans.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS trans 
where trans.P_TRANS_CODE='1011402' 
and trans.BIZ_ID='009' 
union all 
select trans_his.TERM_ID TERM_ID, 
trans_his.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS_HIS trans_his 
where trans_his.P_TRANS_CODE='1011402' 
and trans_his.BIZ_ID='009' 
) trans_pay_lt 
group by trans_pay_lt.TERM_ID 
)trans_pay_lt_l 
on 
trans_basic.APP_TERM_NO=trans_pay_lt_l.TERM_ID 
left join( 
-- 移动缴费 
select 
trans_pay_yd.TERM_ID TERM_ID, 
count(1) PAY_YD_Count, 
sum(trans_pay_yd.TRAN_AMT) PAY_YD_Money 
from( 
select trans.TERM_ID TERM_ID, 
trans.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS trans 
where trans.P_TRANS_CODE='1011402' 
and trans.BIZ_ID='013' 
union all 
select trans_his.TERM_ID TERM_ID, 
trans_his.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS_HIS trans_his 
where trans_his.P_TRANS_CODE='1011402' 
and trans_his.BIZ_ID='013' 
) trans_pay_yd 
group by trans_pay_yd.TERM_ID 
)trans_pay_yd_l 
on 
trans_basic.APP_TERM_NO=trans_pay_yd_l.TERM_ID 
left join( 
-- 查询电信缴费 
select 
trans_pay_dx.TERM_ID TERM_ID, 
count(1) PAY_DX_Count, 
sum(trans_pay_dx.TRAN_AMT) PAY_DX_Money 
from( 
select trans.TERM_ID TERM_ID, 
trans.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS trans 
where trans.P_TRANS_CODE='1011402' 
and trans.BIZ_ID='012' 
union all 
select trans_his.TERM_ID TERM_ID, 
trans_his.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS_HIS trans_his 
where trans_his.P_TRANS_CODE='1011402' 
and trans_his.BIZ_ID='012' 
) trans_pay_dx 
group by trans_pay_dx.TERM_ID 
)trans_pay_dx_l 
on 
trans_basic.APP_TERM_NO=trans_pay_dx_l.TERM_ID 
left join( 
-- 电力缴费 
select 
trans_pay_dl.TERM_ID TERM_ID, 
count(1) PAY_DL_Count, 
sum(trans_pay_dl.TRAN_AMT) PAY_DL_Money 
from( 
select trans.TERM_ID TERM_ID, 
trans.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS trans 
where trans.P_TRANS_CODE='1011402' 
--重庆电力014 三峡电力 008 
and (trans.BIZ_ID='014' or trans.BIZ_ID='008') 
union all 
select trans_his.TERM_ID TERM_ID, 
trans_his.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS_HIS trans_his 
where trans_his.P_TRANS_CODE='1011402' 
--重庆电力014 三峡电力 008 
and (trans_his.BIZ_ID='014' or trans_his.BIZ_ID='008') 
) trans_pay_dl 
group by trans_pay_dl.TERM_ID 
)trans_pay_dl_l 
on 
trans_basic.APP_TERM_NO=trans_pay_dl_l.TERM_ID 
left join( 
-- 自来水缴费 
select 
trans_pay_zls.TERM_ID TERM_ID, 
count(1) PAY_ZLS_Count, 
sum(trans_pay_zls.TRAN_AMT) PAY_ZLS_Money 
from( 
select trans.TERM_ID TERM_ID, 
trans.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS trans 
where trans.P_TRANS_CODE='1011402' 
--水务2测试 004 水费 005 
and (trans.BIZ_ID='004' or trans.BIZ_ID='005') 
union all 
select trans_his.TERM_ID TERM_ID, 
trans_his.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS_HIS trans_his 
where trans_his.P_TRANS_CODE='1011402' 
--水务2测试 004 水费 005 
and (trans_his.BIZ_ID='004' or trans_his.BIZ_ID='005') 
) trans_pay_zls 
group by trans_pay_zls.TERM_ID 
)trans_pay_zls_l 
on 
trans_basic.APP_TERM_NO=trans_pay_zls_l.TERM_ID 
left join( 
-- 燃气缴费 
select 
trans_pay_rq.TERM_ID TERM_ID, 
count(1) PAY_RQ_Count, 
sum(trans_pay_rq.TRAN_AMT) PAY_RQ_Money 
from( 
select trans.TERM_ID TERM_ID, 
trans.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS trans 
where trans.P_TRANS_CODE='1011402' 
--再生资源 003 
and trans.BIZ_ID='003' 
union all 
select trans_his.TERM_ID TERM_ID, 
trans_his.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS_HIS trans_his 
where trans_his.P_TRANS_CODE='1011402' 
--再生资源 003 
and trans_his.BIZ_ID='003' 
) trans_pay_rq 
group by trans_pay_rq.TERM_ID 
)trans_pay_rq_l 
on 
trans_basic.APP_TERM_NO=trans_pay_rq_l.TERM_ID 
left join( 
-- 现金交易 取款 
select 
trans_cash_qk.TERM_ID TERM_ID, 
count(1) CASH_QK_Count, 
sum(trans_cash_qk.TRAN_AMT) CASH_QK_Money 
from( 
select trans.TERM_ID TERM_ID, 
trans.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS trans 
where trans.P_TRANS_CODE='1011101' 
union all 
select trans_his.TERM_ID TERM_ID, 
trans_his.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS_HIS trans_his 
where trans_his.P_TRANS_CODE='1011101' 
) trans_cash_qk 
group by trans_cash_qk.TERM_ID 
)trans_cash_qk_l 
on 
trans_basic.APP_TERM_NO=trans_cash_qk_l.TERM_ID 
left join( 
-- 现金交易 存款 
select 
trans_cash_ck.TERM_ID TERM_ID, 
count(1) CASH_CK_Count, 
sum(trans_cash_ck.TRAN_AMT) CASH_CK_Money 
from( 
select trans.TERM_ID TERM_ID, 
trans.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS trans 
where trans.P_TRANS_CODE='1011103' 
union all 
select trans_his.TERM_ID TERM_ID, 
trans_his.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS_HIS trans_his 
where trans_his.P_TRANS_CODE='1011103' 
) trans_cash_ck 
group by trans_cash_ck.TERM_ID 
)trans_cash_ck_l 
on 
trans_basic.APP_TERM_NO=trans_cash_ck_l.TERM_ID 
left join( 
-- 现金交易 查询 
--余额查询1011001 查询交易明细1011002 积分查询 1011003 
select 
trans_cash_cx.TERM_ID TERM_ID, 
count(1) CASH_CX_Count 
from( 
select trans.TERM_ID TERM_ID 
from SELFCUR.BIZ_MAIN_TRANS trans 
where trans.P_TRANS_CODE='1011001' 
union all 
select trans_his.TERM_ID TERM_ID 
from SELFCUR.BIZ_MAIN_TRANS_HIS trans_his 
where trans_his.P_TRANS_CODE='1011001' 
) trans_cash_cx 
group by trans_cash_cx.TERM_ID 
)trans_cash_cx_l 
on 
trans_basic.APP_TERM_NO=trans_cash_cx_l.TERM_ID 
left join( 
-- 现金交易 转账 
select 
trans_cash_zh.TERM_ID TERM_ID, 
count(1) CASH_ZH_Count, 
sum(trans_cash_zh.TRAN_AMT) CASH_ZH_Money 
from( 
select trans.TERM_ID TERM_ID, 
trans.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS trans 
where trans.P_TRANS_CODE='1011104' 
union all 
select trans_his.TERM_ID TERM_ID, 
trans_his.TRAN_AMT TRAN_AMT 
from SELFCUR.BIZ_MAIN_TRANS_HIS trans_his 
where trans_his.P_TRANS_CODE='1011104' 
) trans_cash_zh 
group by trans_cash_zh.TERM_ID 
) trans_cash_zh_l 
on 
trans_basic.APP_TERM_NO=trans_cash_zh_l.TERM_ID 
left join( 
-- 补登折 存折 
select 
trans_budeng_cz.TERM_ID TERM_ID, 
count(1) BUDENG_CZ_Count 
from( 
select trans.TERM_ID TERM_ID 
from SELFCUR.BIZ_MAIN_TRANS trans 
where trans.P_TRANS_CODE='1011502' 
union all 
select trans_his.TERM_ID TERM_ID 
from SELFCUR.BIZ_MAIN_TRANS_HIS trans_his 
where trans_his.P_TRANS_CODE='1011502' 
) trans_budeng_cz 
group by trans_budeng_cz.TERM_ID 
) trans_budeng_cz_l 
on 
trans_basic.APP_TERM_NO=trans_budeng_cz_l.TERM_ID