转 SQL Union和SQL Union All两者用法区别效率以及与order by 和 group by配合问题

         SQL Union和SQL Union All两者用法区别效率以及与order by 和 group by配合问题

 

SQL Union和SQL Union All用法

SQL UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释：默认地，UNION 操作符选取不同的值，即UNION是去了重的。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

UNION指令的目的是将两个 SQL语句的结果合并起来。从这个角度来看， UNION 跟 JOIN 有些许类似，因为这两个指令都可以由多个表格中撷取资料。union只是将两个结果联结起来一起显示，并不是联结两个表…………

 

UNION 的语法如下：

 [SQL 语句 1]
UNION
[SQL 语句 2] 假设我们有以下的两个表格，

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

Internet Sales 表格

Date	Sales
Jan-07-1999	$250
Jan-10-1999	$535
Jan-11-1999	$320
Jan-12-1999	$750

而我们要找出来所有有营业额 (sales) 的日子。要达到这个目的，我们用以下的 SQL 语句： SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales 结果:

Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

有一点值得注意的是，如果我们在任何一个 SQL 语句 (或是两句都一起) 用 "SELECT DISTINCT Date" 的话，那我们会得到完全一样的结果。

SQL Union All
UNION ALL 这个指令的目的也是要将两个 SQL 语句的结果合并在一起。 UNION ALL 和UNION 不同之处在于 UNION ALL 会将每一笔符合条件的资料都列出来，无论资料值有无重复。UNION ALL 的语法如下： [SQL 语句1]
UNION ALL
[SQL 语句 2] 我们用和上一页同样的例子来显示出UNION ALL 和 UNION 的不同。同样假设我们有以下两个表格，

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

Internet Sales 表格

Date	Sales
Jan-07-1999	$250
Jan-10-1999	$535
Jan-11-1999	$320
Jan-12-1999	$750

而我们要找出有店面营业额以及网络营业额的日子。要达到这个目的，我们用以下的 SQL 语句： SELECT Date FROMStore_Information
UNION ALL
SELECT Date FROM Internet_Sales 结果:

Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-08-1999

Jan-07-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

 另见：

Oracle中的Union与Union All区别

====================================================================================================================================

sql union all的执行效率要比sql union效率要高很多

在数据库中，union和union all 关键字都是将两个结果集合合并为一个，但这两者从使用和效率上来说都有所不同。
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：
select * from gc_dfys
union
select * from ls_jg_dfys
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。
而UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。
从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL，如下：
select * from gc_dfys
union all
select * from ls_jg_dfys

注释：

1、值得注意的是，sql union all的执行效率要比sql union效率要高很多，这是因为，使用sql union需要进行排重，而sql union All 是不需要排重的，这一点非常重要，因为对于一些单纯地使用分表来提高效率的查询，完全可以使用sql union All。
还有，如果使用了union，无论是sql union还是sql union all一定要记住对数据库表加上索引！

2、UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

也就是说，筛选掉重复的记录的过程就是对在表链接后会对所产生的结果集先进行排序运算（order by 表上所有的列），之后根据表的下一行是否与上一行相同来判断下一行是否是重复行而将其删除。

参考：

http://blog.163.com/fly_sky_java/blog/static/14042223420106895310701/

百度   union all用法

==================================================================================================================================

 

union all和order by一起使用出问题

 

select * from (select * from (select zxbz,count(*)rs from dc_jhmy where 1=1 group by zxbz  order by rs desc
    union all
       select 'hj' as zxbz,count(*)rs from dc_jhmy where 1=1)where rownum <= 100

SQL语句如上，我想实现除最后的union all 之前所有的记录先排序，然后在加上最后一条记录，可是   order by rs desc
放在现在的位置报错，命令未正确结束。

 

解决方法如下:利用临时表

 

 

select * from (select * from (select zxbz,count(*)rs from dc_jhmy where  1=1 group by zxbz  order by rs desc)
    union all
       select 'hj' as zxbz,count(*)rs from dc_jhmy where 1=1)where rownum <= 100

参考： 

http://blog.sina.com.cn/s/blog_6cb0deff0100t4l8.html

百度  union all order by

====================================================================================================================================

 

union all和group by的顺序问题

2013-08-27 13:13:28     我来说两句    来源：Sarah Cla的专栏  

union all和group by的顺序问题

 

通过三个实验，来看 union all 及 group by 不同的使用顺序对CPU时间及耗时的影响

 

1. 先Union All， 再Group By

?

1 2 3 4 5 6 7 8 9 10 11

select inctmid ctmid,cnlid,inwhsid whsid,sum(qty) qty,goodsid from (     selectinctmid,cnlid,inwhsid,qty,goodsid        fromDtrBill a,DtrDetail b        wherea.billno=b.billno        unionall        selectinctmid,cnlid,inwhsid,-1*qty qty,goodsid        fromDtrBillRet a,DtrDetailRet b        wherea.billno=b.billno )a group by inctmid,cnlid,inwhsid,goodsid

 

 

2. 分别Group By，再Union All，再Group By

?

1 2 3 4 5 6 7 8 9 10 11 12 13

select inctmid ctmid,cnlid,inwhsid whsid,sum(qty) qty,goodsid from (    selectinctmid,cnlid,inwhsid,sum(qty) qty,goodsid       fromDtrBill a,DtrDetail b       wherea.billno=b.billno       groupby inctmid,cnlid,inwhsid,goodsid       unionall      selectinctmid,cnlid,inwhsid,sum(-1*qty) qty,goodsid      fromDtrBillRet a,DtrDetailRet b      wherea.billno=b.billno      groupby inctmid,cnlid,inwhsid,goodsid )a group by inctmid,cnlid,inwhsid,goodsid

 

 

3. 先Group By，再Union

?

1 2 3 4 5 6 7 8 9

select inctmid,cnlid,inwhsid,sum(qty) qty,goodsid from DtrBill a,DtrDetail b where a.billno=b.billno group by inctmid,cnlid,inwhsid,goodsid union select inctmid,cnlid,inwhsid,sum(-1*qty) qty,goodsid from DtrBillRet a,DtrDetailRet b where a.billno=b.billno group by inctmid,cnlid,inwhsid,goodsid

 

 

4. 执行计划上的不同： 实验 2,3 多了两个分支上的Hash匹配操作

 

5. 实验结果及结论

 

方式

 

CPU时间

 

占用时间

 

结论

 

1

 

2275

 

362

 

并行度高，但最耗CPU资源

 

2

 

1622

 

416

 

并行度中等，最节省CPU资源（貌似是折中的选择）

 

3

 

1811

 

507

 

并行度最低，消耗CPU资源中等

 

 

参考： 

百度   union all group by