开发者社区> 技术小阿哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL Server-聚焦UNIOL ALL/UNION查询

简介:
+关注继续查看
福利推荐:阿里云、腾讯云、华为云等大品牌云产品全线2折优惠活动来袭,4核8G云服务器899元/3年,新老用户共享优惠,点击这里立即抢购>>>

初探UNION和UNION ALL

首先我们过一遍二者的基本概念和使用方法,UNION和UNION ALL是将两个表或者多个表进行JOIN,当然表的数据类型必须相同,对于UNION而言它会去除重复值,而UNION ALL则会返回所有数据,这就是二者的区别和使用方法。下面我们来看一个简单的例子。

USE?TSQL2012
GO--USE?UNION?ALL
SELECT?1
????UNION?ALL?
SELECT?2
????UNION?ALL
SELECT?2
????UNION?ALL
SELECT?3--USE?UNION
SELECT?1
????UNION
SELECT?2
????UNION
SELECT?2
????UNION
SELECT?3

上述我们稍微讲解了下二者的基本使用,接下来我们来看看二者的性能比较。

进一步探讨UNION 和 UNION ALL性能问题

我们首先创建两个测试表Table1和Table2

USE?TSQL2012
GO

CREATE?TABLE?Table1
(
????col?VARCHAR(10)
)

CREATE?TABLE?Table2
(
????col?VARCHAR(10)
)

在表Table1中插入如下测试数据

USE?TSQL2012
GO

INSERT?INTO?Table1
SELECT?'First'UNION?ALL
SELECT?'Second'UNION?ALL
SELECT?'Third'UNION?ALL
SELECT?'Fourth'UNION?ALL
SELECT?'Fifth'

在表Table2中插入如下测试数据

USE?TSQL2012
GO

INSERT?INTO?Table2
SELECT?'First'UNION?ALL
SELECT?'Third'UNION?ALL
SELECT?'Fifth'

我们查询下两个表插入的测试数据

USE?TSQL2012
GO

SELECT?*FROM?Table1

SELECT?*FROM?Table2

接着分别利用UNION和UNION ALL来查询数据比较二者性能开销

USE?TSQL2012
GO--UNION?ALL
SELECT?*FROM?Table1
UNION?ALL
SELECT?*FROM?Table2--UNION
SELECT?*FROM?Table1
UNION
SELECT?*FROM?Table2

?

此时我们能够很明显的看到因为UNION要去除重复所以会进行DISTINCT Sort操作使得其性能要低于UNION ALL。到这里我们可以下个基本结论。

UNION VS UNION ALL性能分析结论:当使用UNION查询语句时类似会进行SELECT DISTINCT操作,除非我们非常明确要返回唯一不重复的值那就用UNION,否则使用UNION ALL会带来更好的性能,返回结果集更快。

是不是到此就完了呢,使用UNION和UNION ALL就这么简单么,那你就太天真了,我们继续往下看。

深入探讨UNION 和 UNION ALL(一)

我们声明一个表变量插入数据并利用UNION ALL来进行查询

USE?TSQL2012
GO

DECLARE?@tempTable?TABLE(col?TEXT)
INSERT?INTO?@tempTable(col)
SELECT?'JeffckyWang'SELECT?col?FROM?@tempTableUNION?ALL?SELECT?'Test?UNION?ALL'

此时对应返回合并结果集,恩,没毛病,我们接下来看看UNION

USE?TSQL2012
GO

DECLARE?@tempTable?TABLE(col?TEXT)
INSERT?INTO?@tempTable(col)
SELECT?'JeffckyWang'SELECT?col?FROM?@tempTableUNION?SELECT?'Test?UNION?ALL'

此时毛病就出来了,说什么数据类型text不可比,不能将其用作UNIN、INTERSERCT或EXCEPT等运算符的操作数,这是什么意思,不太懂。在我们讲解UNION和UNION ALL的性能问题时,我们已经标出UNION的查询计划,UNION会进行DISTINCT Sort操作,这说明什么呢?实际上它内部会进行自动排序同时移除重复的数据,此时数据类型为TEXT所以无法对TEXT类型进行排序,换句话说UNION不支持TEXT类型。所以到这里我们可以给出一个结论。

当利用UNION进行查询时,如果查询列中有TEXT数据类型时,此时会发生错误,因为UNION内部会自动对数据进行排序,而TEXT是无法进行排序的,所以UNION不支持TEXT数据类型。

好了到了这里,我们才算是给出第一个需要注意的地方,下面我们再来看一个。

深入探讨UNION和UNION ALL(二)

当我们对两个表进行UNION ALL时,此时我们如果有这样一个需求,需要使用UNION ALL前后的表是进行排序的,那么此时我们应该如何做呢?下面我们创建测试表看看。

USE?TSQL2012
GO

CREATE?TABLE?Table1?(ID?INT,?Col1?VARCHAR(100));
CREATE?TABLE?Table2?(ID?INT,?Col1?VARCHAR(100));
GO

INSERT?INTO?Table1?(ID,?Col1)
SELECT?1,?'Col1-t1'UNION?ALL
SELECT?2,?'Col2-t1'UNION?ALL
SELECT?3,?'Col3-t1';

INSERT?INTO?Table2?(ID,?Col1)
SELECT?3,?'Col1-t2'UNION?ALL
SELECT?2,?'Col2-t2'UNION?ALL
SELECT?1,?'Col3-t2';
GO

此时我们查询上述Table1和Table2数据如下:

我们的需求是利用UNION ALL将Table1和Table2合并时,其顺序分别是1,2,3和1,2,3。对于UNION查询我们就不用讨论,内部会自行排序,如下则是利用UNION对数据进行排序的结果:

当我们进行UNION ALL时呢

USE?TSQL2012
GO

SELECT?ID,?Col1
FROM?dbo.Table1
  UNION?ALL
SELECT?ID,?Col1
FROM?dbo.Table2
GO

显然满足不了我们的需求,在Table2表中的数据我们需要的是1,2,3。那么我们对Table2中的ID进行ORDER BY结果会如何呢?

USE?TSQL2012
GO

SELECT?ID,?Col1
FROM?dbo.Table1
????UNION?ALL
SELECT?ID,?Col1
FROM?dbo.Table2
ORDER?BY?ID
GO

使用UNION ALL通过对Table2表上的ID进行ORDER BY此时得到的结果和上述UNION查询的结果很类似,但是还是没有得到我们的结果。上述对于两个结果集进行合并后的排序也可以进行如下查询:

USE?TSQL2012
GO

SELECT?*?FROM
(SELECT?ID,?Col1?FROM?dbo.Table1
UNION?ALL
SELECT?ID,?Col1?FROM?dbo.Table2)?as?t
ORDER?BY?ID

对于查询我们能够自定义常量列,我们接下来添加一个额外的常量列,先对其常量列进行排序,然后对ID进行ORDER BY呢,结果又会是怎样的呢?

USE?TSQL2012
GO

SELECT?ID,?Col1,?'addtionalcol1'?AS?addtionalCol?FROM?dbo.Table1
????UNION?ALL
SELECT?ID,?Col1,?'addtionalCol2'?AS?addtionalColFROM?dbo.Table2
ORDER?BY?addtionalCol,?ID
GO

到这里算是基本完成我们的需求,貌似需要额外添加一个列,虽然效果不是太好。



本文转自 sshpp 51CTO博客,原文链接:http://blog.51cto.com/12902932/1926499,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
sqlServer存储过程
1、创建存储过程报错:     'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。 解决方法: use databaseName 后面要加上一句: GO ...
832 0
SQL Server基础之<存储过程>
原文:SQL Server基础之 ??简单来说,存储过程就是一条或者多条sql语句的集合,可视为批处理文件,但是其作用不仅限于批处理。本篇主要介绍变量的使用,存储过程和存储函数的创建,调用,查看,修改以及删除操作。
1480 0
SQLSERVER存储过程语法详解
SQL SERVER存储过程语法: Create?PROC?[?EDURE?]?procedure_name?[?;?number?] ????[?{?@parameter?data_type?} ????????[?VARYING?]?[?=?default?]?[?OUTPUT?] ????]?[?,...n?]   [?WITH ????{?RECOMPILE?|?ENCRY
1662 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
SQL Sever迁移PG经验
立即下载
SQL Server 2017
立即下载
用SQL做数据分析
立即下载


http://www.vxiaotou.com