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(selectmax(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_TYPEfrom(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_TYPEfrom 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    transwhere    trans.TERM_ID=term.APP_TERM_NOand term.TERMINAL_ID=device.TERMINAL_IDand device.MODEL_ID=model.MODEL_IDand term.INST_ID=self.INST_IDand self.PARENT_INST_ID=subbranch.INST_IDand subbranch.PARENT_INST_ID=branch.INST_ID-- 这里需要加入特殊符号来标识，用界面传过来的参数组成sql进行替换--XXXXYYYY--union allselect    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_TYPEfrom 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_hiswhere    trans_his.TERM_ID=term.APP_TERM_NOand term.TERMINAL_ID=device.TERMINAL_IDand device.MODEL_ID=model.MODEL_IDand term.INST_ID=self.INST_IDand self.PARENT_INST_ID=subbranch.INST_IDand subbranch.PARENT_INST_ID=branch.INST_ID-- 这里需要加入特殊符号来标识，用界面传过来的参数组成sql进行替换--XXXXYYYY--)trans_itemgroup by trans_item.APP_TERM_NO)trans_basicleft join(-- 联通缴费selecttrans_pay_lt.TERM_ID TERM_ID,) PAY_LT_Count,sum(trans_pay_lt.TRAN_AMT) PAY_LT_Moneyfrom(select trans.TERM_ID TERM_ID,trans.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS trans''union allselect trans_his.TERM_ID TERM_ID,trans_his.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS_HIS trans_his'') trans_pay_ltgroup by trans_pay_lt.TERM_ID)trans_pay_lt_lontrans_basic.APP_TERM_NO=trans_pay_lt_l.TERM_IDleft join(-- 移动缴费selecttrans_pay_yd.TERM_ID TERM_ID,) PAY_YD_Count,sum(trans_pay_yd.TRAN_AMT) PAY_YD_Moneyfrom(select trans.TERM_ID TERM_ID,trans.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS trans''union allselect trans_his.TERM_ID TERM_ID,trans_his.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS_HIS trans_his'') trans_pay_ydgroup by trans_pay_yd.TERM_ID)trans_pay_yd_lontrans_basic.APP_TERM_NO=trans_pay_yd_l.TERM_IDleft join(-- 查询电信缴费selecttrans_pay_dx.TERM_ID TERM_ID,) PAY_DX_Count,sum(trans_pay_dx.TRAN_AMT) PAY_DX_Moneyfrom(select trans.TERM_ID TERM_ID,trans.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS trans''union allselect trans_his.TERM_ID TERM_ID,trans_his.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS_HIS trans_his'') trans_pay_dxgroup by trans_pay_dx.TERM_ID)trans_pay_dx_lontrans_basic.APP_TERM_NO=trans_pay_dx_l.TERM_IDleft join(-- 电力缴费selecttrans_pay_dl.TERM_ID TERM_ID,) PAY_DL_Count,sum(trans_pay_dl.TRAN_AMT) PAY_DL_Moneyfrom(select trans.TERM_ID TERM_ID,trans.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS trans'--重庆电力014 三峡电力 008')union allselect trans_his.TERM_ID TERM_ID,trans_his.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS_HIS trans_his'--重庆电力014 三峡电力 008')) trans_pay_dlgroup by trans_pay_dl.TERM_ID)trans_pay_dl_lontrans_basic.APP_TERM_NO=trans_pay_dl_l.TERM_IDleft join(-- 自来水缴费selecttrans_pay_zls.TERM_ID TERM_ID,) PAY_ZLS_Count,sum(trans_pay_zls.TRAN_AMT) PAY_ZLS_Moneyfrom(select trans.TERM_ID TERM_ID,trans.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS trans'--水务2测试 004 水费 005')union allselect trans_his.TERM_ID TERM_ID,trans_his.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS_HIS trans_his'--水务2测试 004 水费 005')) trans_pay_zlsgroup by trans_pay_zls.TERM_ID)trans_pay_zls_lontrans_basic.APP_TERM_NO=trans_pay_zls_l.TERM_IDleft join(-- 燃气缴费selecttrans_pay_rq.TERM_ID TERM_ID,) PAY_RQ_Count,sum(trans_pay_rq.TRAN_AMT) PAY_RQ_Moneyfrom(select trans.TERM_ID TERM_ID,trans.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS trans'--再生资源 003'union allselect trans_his.TERM_ID TERM_ID,trans_his.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS_HIS trans_his'--再生资源 003') trans_pay_rqgroup by trans_pay_rq.TERM_ID)trans_pay_rq_lontrans_basic.APP_TERM_NO=trans_pay_rq_l.TERM_IDleft join(-- 现金交易 取款selecttrans_cash_qk.TERM_ID TERM_ID,) CASH_QK_Count,sum(trans_cash_qk.TRAN_AMT) CASH_QK_Moneyfrom(select trans.TERM_ID TERM_ID,trans.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS trans'union allselect trans_his.TERM_ID TERM_ID,trans_his.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS_HIS trans_his') trans_cash_qkgroup by trans_cash_qk.TERM_ID)trans_cash_qk_lontrans_basic.APP_TERM_NO=trans_cash_qk_l.TERM_IDleft join(-- 现金交易 存款selecttrans_cash_ck.TERM_ID TERM_ID,) CASH_CK_Count,sum(trans_cash_ck.TRAN_AMT) CASH_CK_Moneyfrom(select trans.TERM_ID TERM_ID,trans.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS trans'union allselect trans_his.TERM_ID TERM_ID,trans_his.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS_HIS trans_his') trans_cash_ckgroup by trans_cash_ck.TERM_ID)trans_cash_ck_lontrans_basic.APP_TERM_NO=trans_cash_ck_l.TERM_IDleft join(-- 现金交易 查询--余额查询1011001 查询交易明细1011002 积分查询 1011003selecttrans_cash_cx.TERM_ID TERM_ID,) CASH_CX_Countfrom(select trans.TERM_ID TERM_IDfrom SELFCUR.BIZ_MAIN_TRANS trans'union allselect trans_his.TERM_ID TERM_IDfrom SELFCUR.BIZ_MAIN_TRANS_HIS trans_his') trans_cash_cxgroup by trans_cash_cx.TERM_ID)trans_cash_cx_lontrans_basic.APP_TERM_NO=trans_cash_cx_l.TERM_IDleft join(-- 现金交易 转账selecttrans_cash_zh.TERM_ID TERM_ID,) CASH_ZH_Count,sum(trans_cash_zh.TRAN_AMT) CASH_ZH_Moneyfrom(select trans.TERM_ID TERM_ID,trans.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS trans'union allselect trans_his.TERM_ID TERM_ID,trans_his.TRAN_AMT TRAN_AMTfrom SELFCUR.BIZ_MAIN_TRANS_HIS trans_his') trans_cash_zhgroup by trans_cash_zh.TERM_ID) trans_cash_zh_lontrans_basic.APP_TERM_NO=trans_cash_zh_l.TERM_IDleft join(-- 补登折 存折selecttrans_budeng_cz.TERM_ID TERM_ID,) BUDENG_CZ_Countfrom(select trans.TERM_ID TERM_IDfrom SELFCUR.BIZ_MAIN_TRANS trans'union allselect trans_his.TERM_ID TERM_IDfrom SELFCUR.BIZ_MAIN_TRANS_HIS trans_his') trans_budeng_czgroup by trans_budeng_cz.TERM_ID) trans_budeng_cz_lontrans_basic.APP_TERM_NO=trans_budeng_cz_l.TERM_ID