初探UNION和UNION ALL

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

成都創(chuàng)新互聯(lián)公司堅持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站設(shè)計、成都做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時代的舟山網(wǎng)站設(shè)計、移動媒體設(shè)計的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

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

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

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

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

USE TSQL2012
GO

CREATE TABLE Table1
(
    col VARCHAR(10)
)

CREATE TABLE Table2
(
    col VARCHAR(10)
)

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

USE TSQL2012
GO

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

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

USE TSQL2012
GO

SELECT *FROM Table1

SELECT *FROM Table2

接著分別利用UNION和UNION ALL來查詢數(shù)據(jù)比較二者性能開銷

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

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

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

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

深入探討UNION 和 UNION ALL（一）

我們聲明一個表變量插入數(shù)據(jù)并利用UNION ALL來進(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'

此時對應(yīng)返回合并結(jié)果集，恩，沒毛病，我們接下來看看UNION

USE TSQL2012
GO

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

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

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

好了到了這里，我們才算是給出第一個需要注意的地方，下面我們再來看一個。

深入探討UNION和UNION ALL（二）

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

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數(shù)據(jù)如下：

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

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

USE TSQL2012
GO

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

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

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進(jìn)行ORDER BY此時得到的結(jié)果和上述UNION查詢的結(jié)果很類似，但是還是沒有得到我們的結(jié)果。上述對于兩個結(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

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

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

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