SQL Server-聚焦UNIOL ALL/UNION查询
初探UNION和UNION ALL 首先我们过一遍二者的基本概念和使用方法，UNION和UNION ALL是将两个表或者多个表进行JOIN，当然表的数据类型必须相同，对于UNION而言它会去除重复值，而UNION ALL则会返回所有数据，这就是二者的区别和使用方法。下面我们来看一个简单的例子。 USETSQL2012 GO--USEUNIONALL SELECT1 UNIONALL SELECT2 UNIONALL SELECT2 UNIONALL SELECT3--USEUNION SELECT1 UNION SELECT2 UNION SELECT2 UNION SELECT3 上述我们稍微讲解了下二者的基本使用，接下来我们来看看二者的性能比较。 进一步探讨UNION 和 UNION ALL性能问题 我们首先创建两个测试表Table1和Table2 USETSQL2012 GO CREATETABLETable1 ( colVARCHAR(10) ) CREATETABLETable2 ( colVARCHAR(10) ) 在表Table1中插入如下测试数据 USETSQL2012 GO INSERTINTOTable1 SELECT'First'UNIONALL SELECT'Second'UNIONALL SELECT'Third'UNIONALL SELECT'Fourth'UNIONALL SELECT'Fifth' 在表Table2中插入如下测试数据 USETSQL2012 GO INSERTINTOTable2 SELECT'First'UNIONALL SELECT'Third'UNIONALL SELECT'Fifth' 我们查询下两个表插入的测试数据 USETSQL2012 GO SELECT*FROMTable1 SELECT*FROMTable2 接着分别利用UNION和UNION ALL来查询数据比较二者性能开销 USETSQL2012 GO--UNIONALL SELECT*FROMTable1 UNIONALL SELECT*FROMTable2--UNION SELECT*FROMTable1 UNION SELECT*FROMTable2 此时我们能够很明显的看到因为UNION要去除重复所以会进行DISTINCT Sort操作使得其性能要低于UNION ALL。到这里我们可以下个基本结论。 UNION VS UNION ALL性能分析结论：当使用UNION查询语句时类似会进行SELECT DISTINCT操作，除非我们非常明确要返回唯一不重复的值那就用UNION，否则使用UNION ALL会带来更好的性能，返回结果集更快。 是不是到此就完了呢，使用UNION和UNION ALL就这么简单么，那你就太天真了，我们继续往下看。 深入探讨UNION 和 UNION ALL（一） 我们声明一个表变量插入数据并利用UNION ALL来进行查询 USETSQL2012 GO DECLARE@tempTableTABLE(colTEXT) INSERTINTO@tempTable(col) SELECT'JeffckyWang'SELECTcolFROM@tempTableUNIONALLSELECT'TestUNIONALL' 此时对应返回合并结果集，恩，没毛病，我们接下来看看UNION USETSQL2012 GO DECLARE@tempTableTABLE(colTEXT) INSERTINTO@tempTable(col) SELECT'JeffckyWang'SELECTcolFROM@tempTableUNIONSELECT'TestUNIONALL' 此时毛病就出来了，说什么数据类型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前后的表是进行排序的，那么此时我们应该如何做呢？下面我们创建测试表看看。 USETSQL2012 GO CREATETABLETable1(IDINT,Col1VARCHAR(100)); CREATETABLETable2(IDINT,Col1VARCHAR(100)); GO INSERTINTOTable1(ID,Col1) SELECT1,'Col1-t1'UNIONALL SELECT2,'Col2-t1'UNIONALL SELECT3,'Col3-t1'; INSERTINTOTable2(ID,Col1) SELECT3,'Col1-t2'UNIONALL SELECT2,'Col2-t2'UNIONALL SELECT1,'Col3-t2'; GO 此时我们查询上述Table1和Table2数据如下： 我们的需求是利用UNION ALL将Table1和Table2合并时，其顺序分别是1,2,3和1,2,3。对于UNION查询我们就不用讨论，内部会自行排序，如下则是利用UNION对数据进行排序的结果： 当我们进行UNION ALL时呢 USETSQL2012 GO SELECTID,Col1 FROMdbo.Table1 UNIONALL SELECTID,Col1 FROMdbo.Table2 GO 显然满足不了我们的需求，在Table2表中的数据我们需要的是1,2,3。那么我们对Table2中的ID进行ORDER BY结果会如何呢？ USETSQL2012 GO SELECTID,Col1 FROMdbo.Table1 UNIONALL SELECTID,Col1 FROMdbo.Table2 ORDERBYID GO 使用UNION ALL通过对Table2表上的ID进行ORDER BY此时得到的结果和上述UNION查询的结果很类似，但是还是没有得到我们的结果。上述对于两个结果集进行合并后的排序也可以进行如下查询： USETSQL2012 GO SELECT*FROM (SELECTID,Col1FROMdbo.Table1 UNIONALL SELECTID,Col1FROMdbo.Table2)ast ORDERBYID 对于查询我们能够自定义常量列，我们接下来添加一个额外的常量列，先对其常量列进行排序，然后对ID进行ORDER BY呢，结果又会是怎样的呢？ USETSQL2012 GO SELECTID,Col1,'addtionalcol1'ASaddtionalColFROMdbo.Table1 UNIONALL SELECTID,Col1,'addtionalCol2'ASaddtionalColFROMdbo.Table2 ORDERBYaddtionalCol,ID GO 到这里算是基本完成我们的需求，貌似需要额外添加一个列，虽然效果不是太好。 本文转自 sshpp 51CTO博客，原文链接：http://blog.51cto.com/12902932/1926499，如需转载请自行联系原作者
