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