SQL Server-聚焦UNIOL ALL/UNION查詢

初探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

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