(Record merging) Difference between Union and Union all

In the database,UnionAndUnion allThe keywords combine the two results into one, but the two are different in terms of usage and efficiency.

UnionDuplicate records are filtered out after the table link is established. Therefore, after the table link is established, the generated result set is sorted, and duplicate records are deleted before the results are returned.

For example:
Select * From test_union1
Union
Select * From test_union2
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.
WhileUnion allSimply merge the two results and then return 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 test_union1
Union all
Select * From test_union2

There are two basic rules for query result sets using union:

1. The columns and columns in all queries must be in the same order.

2. Data type must be compatible