Shulou(Shulou.com)06/01 Report--

A Preliminary Study of Union and Union All

First of all, we go over the basic concepts and methods of use of the two, UNION and UNION ALL are two tables or multiple tables JOIN, 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 data, this is the difference between the two and the use of methods. Let's look at a simple example.

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

We explained the basic use of the two slightly above, and then we will look at the performance comparison of the two.

Further explore UNION and UNION ALL performance issues

We first create two test tables, Table1 and Table2.

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

Insert the following test data into Table1

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

Insert the following test data into Table2

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

We query the test data inserted by the next two tables

USE TSQL2012GOSELECT *FROM Table1SELECT *FROM Table2

Then, UNION and UNION ALL are used to query data and compare their performance costs

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

At this point we can clearly see that UNION will perform DISTINCT Sort operation because it needs to remove duplicates, making its performance lower than UNION ALL. Here we can draw a basic conclusion.

UNION VS UNION ALL Performance Analysis Conclusion: When using UNION query statements, similar SELECT DISTINCT operations will be performed, unless we are very clear that we want to return unique values that are not repeated, then use UNION, otherwise using UNION ALL will bring better performance and return faster result sets.

Is it over here, using UNION and UNION ALL is so simple, then you are too naive, we continue to look down.

Further discussion of Union and Union ALL (1)

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

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

At this time, the corresponding return merge result set, um, no problem, let's look at UNION next

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

At this point the problem came out, saying that what data type text is not comparable, can not be used as UNIN, INTERSERCT or EXCEPT operators operand, what does this mean, do not quite understand. When we explained the performance problems of UNION and UNION ALL, we have marked the query plan of UNION, and UNION will perform DISTINCT Sort operation. What does this mean? In fact, it automatically sorts internally and removes duplicate data. In this case, the data type is TEXT, so it cannot sort TEXT types. In other words, UNION does not support TEXT types. So here we can draw a conclusion.

When using UNION to query, if there is a TEXT data type in the query column, an error will occur at this time, because UNION will automatically sort the data internally, and TEXT cannot be sorted, so UNION does not support the TEXT data type.

Well, here we are, giving the first place to pay attention to, let's look at one more.

Further discussion of Union and Union All (II)

When we perform UNION ALL on two tables, if we have such a requirement at this time, we need to use UNION ALL before and after the table is sorted, then what should we do at this time? Let's create a test table to see.

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

The Table1 and Table2 data are as follows:

Our requirement is to merge Table1 and Table2 using UNION ALL in the order of 1, 2, 3 and 1, 2, 3 respectively. For UNION query we do not need to discuss, internal sorting will be self-sorting, the following is the use of UNION to sort the data results:

What about when we do UNION ALL?

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

Obviously, this doesn't meet our needs. The data in Table2 we need is 1, 2, 3. So what happens if we do ORDER BY on the IDs in Table2?

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

Using UNION ALL to ORDER BY the IDs in Table2 yields results similar to those of the UNION query above, but we still don't get our results. The above sorting after merging the two result sets can also be queried as follows:

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

For queries we can customize the constant column, we next add an extra constant column, first sort the constant column, and then ORDER BY the ID, what will the result be?

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

This is basically complete our requirements, it seems that we need to add an additional column, although the effect is not too good.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.