SQL Server-聚焦UNIOL ALL/UNION查询

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:
+关注继续查看

初探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,如需转载请自行联系原作者

相关实践学习
使用交互方式创建数据表
本次实验主要介绍如何在RDS-SQLServer数据库中使用交互方式创建数据表。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
2天前
|
SQL 关系型数据库 MySQL
这个错误可能是由于Flink CDC在处理MySQL和SQL Server之间的数据同步时遇到了问题
这个错误可能是由于Flink CDC在处理MySQL和SQL Server之间的数据同步时遇到了问题
3 1
|
2天前
|
SQL Oracle 关系型数据库
选择适合您网站的 SQL 托管:MS SQL Server、Oracle、MySQL
如果您希望您的网站能够存储和检索数据,您的Web服务器应该能够访问使用SQL语言的数据库系统。以下是一些常见的SQL托管选项:
18 2
|
4天前
|
SQL 关系型数据库 MySQL
SQL Server重置自增的值为0
SQL Server重置自增的值为0
16 0
|
4天前
|
SQL 关系型数据库 Apache
Microsoft SQL Server 迁移到 PostgreSQL 利器:Babelfish
Babelfish for PostgreSQL 加速了将 Microsoft SQL Server 应用程序迁移到 PostgreSQL 的旅程。Babelfish for PostgreSQL 是一个开源项目,可在 Apache 2.0 和 PostgreSQL 许可下使用。它使 PostgreSQL 能够理解来自 Microsoft SQL Server 编写的应用程序的查询。Babelfish 了解 SQL Server 有线协议和 Microsoft SQL Server 查询语言 T-SQL,此您不必切换数据库驱动程序或重新编写所有应用程序查询。
170 0
|
4天前
|
SQL 数据库
SQL Server 数据查询
SQL Server 数据查询
60 0
|
4天前
|
SQL 数据库 数据安全/隐私保护
SQL Server 数据操控,视图和索引
SQL Server 数据操控,视图和索引
68 0
|
4天前
|
SQL Go 数据库
SQL Server创建及修改表实验报告(下)
SQL Server创建及修改表实验报告(上)
18 0
|
4天前
|
SQL 数据库
SQL Server 创建和管理数据表
SQL Server 创建和管理数据表
26 1
|
4天前
|
SQL 数据库
SQL Server 连接查询和子查询
SQL Server 连接查询和子查询
56 0
|
4天前
|
SQL 数据库
SQL Server 单表数据查询
SQL Server 单表数据查询
73 1
相关产品
云迁移中心
推荐文章
更多