SQL Server-聚焦UNIOL ALL/UNION查詢

標籤：sql server-聚焦uniol all/union查詢

初探UNION和UNION ALL

首先我們過一遍二者的基本概念和使用方法，UNION和UNION ALL是將兩個表或者多個表進行JOIN，當然表的資料類型必須相同，對於UNION而言它會去除重複值，而UNION ALL則會返回所有資料，這就是二者的區別和使用方法。下面我們來看一個簡單的例子。

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

USE TSQL2012GO--USE UNION ALLSELECT 1    UNION ALL SELECT 2    UNION ALLSELECT 2    UNION ALLSELECT 3--USE UNIONSELECT 1    UNIONSELECT 2    UNIONSELECT 2    UNIONSELECT 3

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

650) this.width=650;" src="http://images2015.cnblogs.com/blog/589642/201612/589642-20161216134540729-1281144202.png" style="margin:0px;padding:0px;border:0px;" />

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

進一步探討UNION 和 UNION ALL效能問題

我們首先建立兩個測試表Table1和Table2

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

USE TSQL2012GOCREATE TABLE Table1(    col VARCHAR(10))CREATE TABLE Table2(    col VARCHAR(10))

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

在表Table1中插入如下測試資料

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

USE TSQL2012GOINSERT INTO Table1SELECT ‘First‘UNION ALLSELECT ‘Second‘UNION ALLSELECT ‘Third‘UNION ALLSELECT ‘Fourth‘UNION ALLSELECT ‘Fifth‘

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

在表Table2中插入如下測試資料

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

USE TSQL2012GOINSERT INTO Table2SELECT ‘First‘UNION ALLSELECT ‘Third‘UNION ALLSELECT ‘Fifth‘

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

我們查詢下兩個表插入的測試資料

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

USE TSQL2012GOSELECT *FROM Table1SELECT *FROM Table2

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

650) this.width=650;" src="http://images2015.cnblogs.com/blog/589642/201612/589642-20161216135325823-715023008.png" style="margin:0px;padding:0px;border:0px;" />

接著分別利用UNION和UNION ALL來查詢資料比較二者效能開銷

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

USE TSQL2012GO--UNION ALLSELECT *FROM Table1UNION ALLSELECT *FROM Table2--UNIONSELECT *FROM Table1UNIONSELECT *FROM Table2

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

650) this.width=650;" src="http://images2015.cnblogs.com/blog/589642/201612/589642-20161216135556620-1899475508.png" style="margin:0px;padding:0px;border:0px;" />

 

650) this.width=650;" src="http://images2015.cnblogs.com/blog/589642/201612/589642-20161216135735511-658373016.png" style="margin:0px;padding:0px;border:0px;" />

此時我們能夠很明顯的看到因為UNION要去除重複所以會進行DISTINCT Sort操作使得其效能要低於UNION ALL。到這裡我們可以下個基本結論。

UNION VS UNION ALL效能分析結論：當使用UNION查詢語句時類似會進行SELECT DISTINCT操作，除非我們非常明確要返回唯一不重複的值那就用UNION，否則使用UNION ALL會帶來更好的效能，返回結果集更快。

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

深入探討UNION 和 UNION ALL（一）

我們聲明一個表變數插入資料並利用UNION ALL來進行查詢

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

USE TSQL2012GODECLARE @tempTable TABLE(col TEXT)INSERT INTO @tempTable(col)SELECT ‘JeffckyWang‘SELECT col FROM @tempTableUNION ALL SELECT ‘Test UNION ALL‘

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

650) this.width=650;" src="http://images2015.cnblogs.com/blog/589642/201612/589642-20161216141449558-1037291423.png" style="margin:0px;padding:0px;border:0px;" />

此時對應返回合并結果集，恩，沒毛病，我們接下來看看UNION

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

USE TSQL2012GODECLARE @tempTable TABLE(col TEXT)INSERT INTO @tempTable(col)SELECT ‘JeffckyWang‘SELECT col FROM @tempTableUNION SELECT ‘Test UNION ALL‘

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

650) this.width=650;" src="http://images2015.cnblogs.com/blog/589642/201612/589642-20161216141559636-1029142704.png" style="margin:0px;padding:0px;border:0px;" />

此時毛病就出來了，說什麼資料類型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前後的表是進行排序的，那麼此時我們應該如何做呢？下面我們建立測試表看看。

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

USE TSQL2012GOCREATE TABLE Table1 (ID INT, Col1 VARCHAR(100));CREATE TABLE Table2 (ID INT, Col1 VARCHAR(100));GOINSERT INTO Table1 (ID, Col1)SELECT 1, ‘Col1-t1‘UNION ALLSELECT 2, ‘Col2-t1‘UNION ALLSELECT 3, ‘Col3-t1‘;INSERT INTO Table2 (ID, Col1)SELECT 3, ‘Col1-t2‘UNION ALLSELECT 2, ‘Col2-t2‘UNION ALLSELECT 1, ‘Col3-t2‘;GO

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

此時我們查詢上述Table1和Table2資料如下：

650) this.width=650;" src="http://images2015.cnblogs.com/blog/589642/201612/589642-20161216143231104-1683696895.png" style="margin:0px;padding:0px;border:0px;" />

我們的需求是利用UNION ALL將Table1和Table2合并時，其順序分別是1,2,3和1,2,3。對於UNION查詢我們就不用討論，內部會自行排序，如下則是利用UNION對資料進行排序的結果：

650) this.width=650;" src="http://images2015.cnblogs.com/blog/589642/201612/589642-20161216143600948-2086819970.png" style="margin:0px;padding:0px;border:0px;" />

當我們進行UNION ALL時呢

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

USE TSQL2012GOSELECT ID, Col1FROM dbo.Table1　　UNION ALLSELECT ID, Col1FROM dbo.Table2GO

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

650) this.width=650;" src="http://images2015.cnblogs.com/blog/589642/201612/589642-20161216143644433-1984975894.png" style="margin:0px;padding:0px;border:0px;" />

顯然滿足不了我們的需求，在Table2表中的資料我們需要的是1,2,3。那麼我們對Table2中的ID進行ORDER BY結果會如何呢？

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

USE TSQL2012GOSELECT ID, Col1FROM dbo.Table1    UNION ALLSELECT ID, Col1FROM dbo.Table2ORDER BY IDGO

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

650) this.width=650;" src="http://images2015.cnblogs.com/blog/589642/201612/589642-20161216144004870-1881014960.png" style="margin:0px;padding:0px;border:0px;" />

使用UNION ALL通過對Table2表上的ID進行ORDER BY此時得到的結果和上述UNION查詢的結果很類似，但是還是沒有得到我們的結果。上述對於兩個結果集進行合并後的排序也可以進行如下查詢：

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

USE TSQL2012GOSELECT * FROM(SELECT ID, Col1 FROM dbo.Table1UNION ALLSELECT ID, Col1 FROM dbo.Table2) as tORDER BY ID

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

650) this.width=650;" src="http://images2015.cnblogs.com/blog/589642/201612/589642-20161216145704808-647215451.png" style="margin:0px;padding:0px;border:0px;" />

對於查詢我們能夠自訂常量列，我們接下來添加一個額外的常量列，先對其常量列進行排序，然後對ID進行ORDER BY呢，結果又會是怎樣的呢？

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

USE TSQL2012GOSELECT ID, Col1, ‘addtionalcol1‘ AS addtionalCol FROM dbo.Table1    UNION ALLSELECT ID, Col1, ‘addtionalCol2‘ AS addtionalColFROM dbo.Table2ORDER BY addtionalCol, IDGO

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

650) this.width=650;" src="http://images2015.cnblogs.com/blog/589642/201612/589642-20161216144904386-1733147179.png" style="margin:0px;padding:0px;border:0px;" />

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

SQL Server-聚焦UNIOL ALL/UNION查詢