About UNION all and union usage and differences

(Transferred from: http://www.cnblogs.com/EricaMIN1987_IT/archive/2011/01/20/1940188.html)

The purpose of the Union directive is to combine the results of two SQL statements. From this point of view, we have this feeling that union and join seem to be similar, because these two instructions can be retrieved from multiple tables. One limitation of the Union is that the columns generated by the two SQL statements need to be of the same type of data. In addition, when we use the Union command, we will only see different data values (similarSELECTDISTINCT).    The union simply joins the two results together and does not join two tables. Suppose we have the following two tables: Store_information table: Store_name Sales Date Los Angeles $ thejan- to-1999San Diego $ -jan- --1999Los Angeles $ -jan- ,-1999Boston $ thejan- ,-1999Internet Sales table: Date sales Jan- --1999$ -Jan-Ten-1999$535Jan- One-1999$ theJan- A-1999$ -if we want to find out all the days that have turnover (sales). To achieve this, we use the following SQL statement:SELECTDate from Store_information UNIONSELECTDate from Internet_sales results:Date Jan- to-1999Jan- --1999Jan- ,-1999Jan-Ten-1999Jan- One-1999Jan- A-1999The Union will filter out duplicate records after the table link is made, so the resulting set of results will be sorted after the table is connected, the duplicate records are deleted and the results returned. The purpose of the UNION all directive is to merge the results of the two SQL statements together. The Union all and union differ in that union all lists each qualifying material, regardless of whether the data value is duplicated or not. If we only use union ALL in the above example, the result would be: Date Jan- to-1999Jan- --1999Jan- ,-1999Jan- ,-1999Jan- --1999Jan-Ten-1999Jan- One-1999Jan- A-1999UNION All simply merges two results and returns. Thus, if there are duplicate data in the two result sets returned, the returned result set will contain duplicate data. In terms of efficiency, SQL UNION ALL performs much more efficiently than SQL Union because it is important to use SQL Union to perform a row, and the SQL union all does not require a row weight, because for some queries that simply use a table to improve efficiency , you can use SQL union all entirely. 

About UNION all and union usage and differences