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