Difference between Union and Union all in the database

This evening, we found the difference between the two tables. Haha.

Union concatenates two tables and deletes their repeated items;

Union all does not delete the duplicate items of the two tables.

This is simple. But also record it. It is a small achievement.

Additional information:

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

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 users1 Union select * From user2
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 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 user1 Union all select * From user2