SQL Server-聚焦UNIOL ALL/UNION查詢

初探UNION和UNION ALL

首先我們過(guò)一遍二者的基本概念和使用方法，UNION和UNION ALL是將兩個(gè)表或者多個(gè)表進(jìn)行JOIN，當(dāng)然表的數(shù)據(jù)類(lèi)型必須相同，對(duì)于UNION而言它會(huì)去除重復(fù)值，而UNION ALL則會(huì)返回所有數(shù)據(jù)，這就是二者的區(qū)別和使用方法。下面我們來(lái)看一個(gè)簡(jiǎn)單的例子。

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

上述我們稍微講解了下二者的基本使用，接下來(lái)我們來(lái)看看二者的性能比較。

進(jìn)一步探討UNION 和 UNION ALL性能問(wèn)題

我們首先創(chuàng)建兩個(gè)測(cè)試表Table1和Table2

USE TSQL2012
GO

CREATE TABLE Table1
(
    col VARCHAR(10)
)

CREATE TABLE Table2
(
    col VARCHAR(10)
)

在表Table1中插入如下測(cè)試數(shù)據(jù)

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中插入如下測(cè)試數(shù)據(jù)

USE TSQL2012
GO

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

我們查詢下兩個(gè)表插入的測(cè)試數(shù)據(jù)

USE TSQL2012
GO

SELECT *FROM Table1

SELECT *FROM Table2

接著分別利用UNION和UNION ALL來(lái)查詢數(shù)據(jù)比較二者性能開(kāi)銷(xiāo)

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

此時(shí)我們能夠很明顯的看到因?yàn)閁NION要去除重復(fù)所以會(huì)進(jìn)行DISTINCT Sort操作使得其性能要低于UNION ALL。到這里我們可以下個(gè)基本結(jié)論。

UNION VS UNION ALL性能分析結(jié)論：當(dāng)使用UNION查詢語(yǔ)句時(shí)類(lèi)似會(huì)進(jìn)行SELECT DISTINCT操作，除非我們非常明確要返回唯一不重復(fù)的值那就用UNION，否則使用UNION ALL會(huì)帶來(lái)更好的性能，返回結(jié)果集更快。

是不是到此就完了呢，使用UNION和UNION ALL就這么簡(jiǎn)單么，那你就太天真了，我們繼續(xù)往下看。

深入探討UNION 和 UNION ALL（一）

我們聲明一個(gè)表變量插入數(shù)據(jù)并利用UNION ALL來(lái)進(jìn)行查詢

USE TSQL2012
GO

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

此時(shí)對(duì)應(yīng)返回合并結(jié)果集，恩，沒(méi)毛病，我們接下來(lái)看看UNION

USE TSQL2012
GO

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

此時(shí)毛病就出來(lái)了，說(shuō)什么數(shù)據(jù)類(lèi)型text不可比，不能將其用作UNIN、INTERSERCT或EXCEPT等運(yùn)算符的操作數(shù)，這是什么意思，不太懂。在我們講解UNION和UNION ALL的性能問(wèn)題時(shí)，我們已經(jīng)標(biāo)出UNION的查詢計(jì)劃，UNION會(huì)進(jìn)行DISTINCT Sort操作，這說(shuō)明什么呢？實(shí)際上它內(nèi)部會(huì)進(jìn)行自動(dòng)排序同時(shí)移除重復(fù)的數(shù)據(jù)，此時(shí)數(shù)據(jù)類(lèi)型為T(mén)EXT所以無(wú)法對(duì)TEXT類(lèi)型進(jìn)行排序，換句話說(shuō)UNION不支持TEXT類(lèi)型。所以到這里我們可以給出一個(gè)結(jié)論。

當(dāng)利用UNION進(jìn)行查詢時(shí)，如果查詢列中有TEXT數(shù)據(jù)類(lèi)型時(shí)，此時(shí)會(huì)發(fā)生錯(cuò)誤，因?yàn)閁NION內(nèi)部會(huì)自動(dòng)對(duì)數(shù)據(jù)進(jìn)行排序，而TEXT是無(wú)法進(jìn)行排序的，所以UNION不支持TEXT數(shù)據(jù)類(lèi)型。

好了到了這里，我們才算是給出第一個(gè)需要注意的地方，下面我們?cè)賮?lái)看一個(gè)。

深入探討UNION和UNION ALL（二）

當(dāng)我們對(duì)兩個(gè)表進(jìn)行UNION ALL時(shí)，此時(shí)我們?nèi)绻羞@樣一個(gè)需求，需要使用UNION ALL前后的表是進(jìn)行排序的，那么此時(shí)我們應(yīng)該如何做呢？下面我們創(chuàng)建測(cè)試表看看。

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

此時(shí)我們查詢上述Table1和Table2數(shù)據(jù)如下：

我們的需求是利用UNION ALL將Table1和Table2合并時(shí)，其順序分別是1,2,3和1,2,3。對(duì)于UNION查詢我們就不用討論，內(nèi)部會(huì)自行排序，如下則是利用UNION對(duì)數(shù)據(jù)進(jìn)行排序的結(jié)果：

當(dāng)我們進(jìn)行UNION ALL時(shí)呢

USE TSQL2012
GO

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

顯然滿足不了我們的需求，在Table2表中的數(shù)據(jù)我們需要的是1,2,3。那么我們對(duì)Table2中的ID進(jìn)行ORDER BY結(jié)果會(huì)如何呢？

USE TSQL2012
GO

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

使用UNION ALL通過(guò)對(duì)Table2表上的ID進(jìn)行ORDER BY此時(shí)得到的結(jié)果和上述UNION查詢的結(jié)果很類(lèi)似，但是還是沒(méi)有得到我們的結(jié)果。上述對(duì)于兩個(gè)結(jié)果集進(jìn)行合并后的排序也可以進(jìn)行如下查詢：

USE TSQL2012
GO

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

對(duì)于查詢我們能夠自定義常量列，我們接下來(lái)添加一個(gè)額外的常量列，先對(duì)其常量列進(jìn)行排序，然后對(duì)ID進(jìn)行ORDER BY呢，結(jié)果又會(huì)是怎樣的呢？

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

到這里算是基本完成我們的需求，貌似需要額外添加一個(gè)列，雖然效果不是太好。