SQL server-Focus uniol all/union Query

Preliminary study on union and UNION all

First we go through the basic concepts and methods of use, the Union and union all are joins of two tables or tables, and of course the data type of the table must be the same, for union it will remove duplicate values, and union ALL will return all the data. This is the difference between the two and how to use it. Let's look at a simple example.

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "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=" Copy Code "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; "/>

We have explained the basic use of the two in a bit, and then we will look at the performance comparison.

Further discussion of the Union and union all performance issues

We first create two test tables Table1 and Table2

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use Tsql2012gocreate table Table1 (col varchar) CREATE TABLE Table2 (col varchar (10))

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Insert the following test data in the table Table1

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "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=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Insert the following test data in the table Table2

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "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=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

We query the next two tables to insert the test data

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use Tsql2012goselect *from table1select *from Table2

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "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; "/>

Then the Union and union all are used to query the data to compare the performance cost.

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "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=" Copy Code "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; "/>

At this point we can clearly see that because the Union is de-duplicated, the distinct sort operation makes it less performance than union all. Here we can come to the next basic conclusion.

Union VS UNION ALL performance Analysis conclusion: When a UNION query statement is used, a select distinct operation is similar, unless we are very clear that we want to return a unique, distinct value then use union, otherwise the union all will result in better performance. Returns the result set faster.

Is this the end of it, it's so simple to use Union and union all, then you're naïve and we keep looking down.

Deep discussion of union and Union ALL (i)

We declare a table variable to insert the data and use union ALL to query

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "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=" Copy Code "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; "/>

The corresponding return merge result set at this time, yes, no problem, let's look at Union now.

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "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=" Copy Code "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; "/>

At this point, the problem comes out, say what data type text is not comparable, you can not use it as Unin, INTERSERCT, or except operators such as operands, what this means, do not understand. When we talk about the performance of Union and union all, we've marked the Union's query plan, and Union does the distinct sort operation, what does that mean? In fact, it automatically sorts and removes duplicate data at the same time, where the data type is text so the text type cannot be sorted, in other words, the Union does not support the text type. So here we can give a conclusion.

When a query is made with union, an error occurs when there is a text data type in the query column, because the data is automatically sorted internally by the Union, and text cannot be sorted, so the text data type is not supported by union.

Well, here we are to give the first place to pay attention, let's see one more.

Deep discussion of union and Union All (ii)

When we union all on two tables, when we have a requirement that the table before and after the union all is sorted, what should we do at this point? Below we create a test table to see.

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "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 Tab Le2 (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=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

At this point we query the above Table1 and Table2 data as follows:

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

Our demand is to combine Table1 and Table2 with union all, in the order of three-and-three. For union queries, we don't have to discuss the internal ordering itself, and the following is the result of sorting the data using union:

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

When we're in union all?

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "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=" Copy Code "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; "/>

Obviously not enough to meet our needs, the data in the Table2 table we need is in the form of a. What about the order by result of the ID in Table2?

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "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=" Copy Code "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; "/>

Use UNION ALL to order by the ID on the Table2 table the result is similar to the result of the union query above, but it still does not get our results. The following query can also be made for the combined sorting of two result sets:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use Tsql2012goselect * FROM (the 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=" Copy Code "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; "/>

For the query we are able to customize the constant column, we then add an extra constant column, sort its constant column first, and then order by for the ID, what will the result be?

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Use the 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=" Copy Code "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; "/>

Here is basically to complete our requirements, seemingly need to add a column, although the effect is not very good.

SQL server-Focus uniol all/union Query