The difference between a union and the union All in SQL

When writing SQL query statements, you often encounter a requirement similar to this: to query male workers older than 60 years old and all employees born in 1950. When dealing with this requirement, it is not possible to use a simple SQL statement to query out all the results that satisfy the condition, then we need to divide this requirement into several small sub-requirements, then merge the result collection of the subquery to get the result satisfying the query condition. To handle this kind of requirement, SQL provides the Union and union all operations, as follows:

SELECT * from the employee  where gender= ' M ' and Year (' 2013-09-21 ')-year (birthday) >    Union (All) c4/>* from the  employee   where year (birthday)=1950 with   ur   ; --Fragment One  

So what's the difference between these two operations, see the following example, in order to validate the example we create an employee table and add some data to it, the sample code looks like this:

NULL NULL ,   Char Char (), Mail varchar (20); --Fragment 2  

INSERT into employee values  (1, ' 1989-01-01 ', ' m ', ' Lee ', ' 1345663221 ', ' [email protected] '),  (2, ' 1952-01-01 ', ' m ', ' Li er ', ' 1345663331 ', ' [email protected] '),  (3, ' 1951-01-01 ', ' F ', ' Lie Triple ', ' 1345664311 ', ' [email protected] '  ),  (4, ' 1950-01-01 ', ' M ', ' John Doe ', ' 1345662111 ', ' [email protected] '),  (5, ' 1950-07-01 ', ' F ', ' Lee ', ' 1345343221 ', ' [email protected] ';  --Fragment Three  

Performing the union and Union all operations in sample snippet one, respectively, results in the following:

From the results of the above query, we can see that the union all operation is simply to get the two subquery result set directly and operation, and will not eliminate the two result sets of duplicates, and the union operation in addition to the elimination of the result set of duplicate parts, The result set is also sorted (the actual logic of execution should be to sort a child result set first, then determine if there is duplicate data, and if so, delete the duplicate data).

Small tips: Because the Union needs to sort the query result set, when the amount of data is large, if it is not special need, try not to use the union operation, instead of the union all operation, and then the result of the Union all out to perform a deduplication operation, This will greatly enhance the efficiency of the query.

The difference between a union and the union All in SQL