SQLServer-聚焦UNIOLALL/UNION查询

初探UNION和UNION ALL

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

成都创新互联公司坚持“要么做到,要么别承诺”的工作理念,服务领域包括:成都网站设计、成都做网站、企业官网、英文网站、手机端网站、网站推广等服务,满足客户于互联网时代的舟山网站设计、移动媒体设计的需求,帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴!

SQL Server-聚焦UNIOL ALL/UNION查询

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

SQL Server-聚焦UNIOL ALL/UNION查询

SQL Server-聚焦UNIOL ALL/UNION查询

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

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

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

SQL Server-聚焦UNIOL ALL/UNION查询

USE TSQL2012
GO

CREATE TABLE Table1
(
    col VARCHAR(10)
)

CREATE TABLE Table2
(
    col VARCHAR(10)
)

SQL Server-聚焦UNIOL ALL/UNION查询

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

SQL Server-聚焦UNIOL ALL/UNION查询

USE TSQL2012
GO

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

SQL Server-聚焦UNIOL ALL/UNION查询

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

SQL Server-聚焦UNIOL ALL/UNION查询

USE TSQL2012
GO

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

SQL Server-聚焦UNIOL ALL/UNION查询

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

SQL Server-聚焦UNIOL ALL/UNION查询

USE TSQL2012
GO

SELECT *FROM Table1

SELECT *FROM Table2

SQL Server-聚焦UNIOL ALL/UNION查询

SQL Server-聚焦UNIOL ALL/UNION查询

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

SQL Server-聚焦UNIOL ALL/UNION查询

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

SQL Server-聚焦UNIOL ALL/UNION查询

SQL Server-聚焦UNIOL ALL/UNION查询

 

SQL Server-聚焦UNIOL ALL/UNION查询

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

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

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

深入探讨UNION 和 UNION ALL(一)

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

SQL Server-聚焦UNIOL ALL/UNION查询

USE TSQL2012
GO

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

SQL Server-聚焦UNIOL ALL/UNION查询

SQL Server-聚焦UNIOL ALL/UNION查询

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

SQL Server-聚焦UNIOL ALL/UNION查询

USE TSQL2012
GO

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

SQL Server-聚焦UNIOL ALL/UNION查询

SQL Server-聚焦UNIOL ALL/UNION查询

此时毛病就出来了,说什么数据类型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前后的表是进行排序的,那么此时我们应该如何做呢?下面我们创建测试表看看。

SQL Server-聚焦UNIOL ALL/UNION查询

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

SQL Server-聚焦UNIOL ALL/UNION查询

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

SQL Server-聚焦UNIOL ALL/UNION查询

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

SQL Server-聚焦UNIOL ALL/UNION查询

当我们进行UNION ALL时呢

SQL Server-聚焦UNIOL ALL/UNION查询

USE TSQL2012
GO

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

SQL Server-聚焦UNIOL ALL/UNION查询

SQL Server-聚焦UNIOL ALL/UNION查询

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

SQL Server-聚焦UNIOL ALL/UNION查询

USE TSQL2012
GO

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

SQL Server-聚焦UNIOL ALL/UNION查询

SQL Server-聚焦UNIOL ALL/UNION查询

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

SQL Server-聚焦UNIOL ALL/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

SQL Server-聚焦UNIOL ALL/UNION查询

SQL Server-聚焦UNIOL ALL/UNION查询

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

SQL Server-聚焦UNIOL ALL/UNION查询

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

SQL Server-聚焦UNIOL ALL/UNION查询

SQL Server-聚焦UNIOL ALL/UNION查询

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


网站题目:SQLServer-聚焦UNIOLALL/UNION查询
文章出自:http://pwwzsj.com/article/gcgejh.html