Use of union and Union all in MySQL

From: http://hi.baidu.com/dujiaopeng/item/b87de8a952956e9d151073b6

 

In the database, the Union and Union all keywords combine the two results into one, but both have different usage and efficiency.

Union in MySQL

Union filters out duplicate records after table link. Therefore, after table link, it sorts the generated result sets and deletes duplicate records before returning results. In most applications, duplicate records are not generated. The most common is the union of Process Tables and historical tables. For example:

Select * From gc_dfys Union select * From ls_jg_dfys

This SQL statement extracts the results of two tables at run time, sorts and deletes duplicate records using the sorting space, and finally returns the result set. If the table has a large amount of data, it may cause disk sorting.

Union all in MySQL

Union all simply merges the two results and returns them. In this way, if duplicate data exists in the two returned result sets, the returned result sets will contain duplicate data.

In terms of efficiency, Union all is much faster than Union. Therefore, if you can confirm that the two results of the merge do not contain duplicate data, use Union all, as shown below:

Select * From gc_dfys Union all select * From ls_jg_dfys

 

If Union is used, all returned rows are unique, as if you have used distinct for the entire result set
If Union all is used, all rows are returned.

If you want to use the order by or limit clause to classify or limit all Union results, parentheses should be added to a single SELECT statement, and put order by or limit behind the last one:
(Select a from tbl_name where a = 10 and B = 1)
Union
(Select a from tbl_name where a = 11 and B = 2)
Order by a limit 10;
You can do this in a bit of trouble:
Select userid from (
Select userid from Testa Union all select userid from testb) T
Order by userid limit 0, 1;

If you still want to group by and have other conditions, then:
Select userid from (select userid from Testa Union all select userid from testb) T group by userid having count (userid) = 2;

Note: there must be an alias behind the brackets of Union. Otherwise, an error is reported.

Of course, if the data size of several tables in union is large, we recommend that you export the text in the pilot mode and execute it using scripts.
Because SQL is purely used, the efficiency will be relatively low, and it will write temporary files. If your disk space is not large enough, it may cause errors.
Error writing File '/tmp/mylsivgk' (errcode: 28)