The difference between union and union all in Database

Union deletes duplicates after joining two tables

Union all joins both tables without removing their duplicates.

In the database, both UNION and UNION ALL merge two result sets into one, but they are different in terms of usage and efficiency.

UNION filters out duplicate records after table linking, so it sorts the resulting result set after table linking, deletes duplicate records and returns the result. Most practical applications will not produce duplicate records, the most common is the process table and history table UNION. Such as:

Select * from users1 union select * from user2

This SQL first takes out the results of the two tables at run time, then sorts the duplicate records with the sort space, and finally returns the result set, which may lead to sorting by disk if the table has a large amount of data.

UNION ALL simply merges the two results and returns. In this way, if there is duplicate data in the two result sets returned, the returned result set will contain duplicate data.

In terms of efficiency, UNION ALL is much faster than UNION, so if you can confirm that the two merged result sets do not contain duplicate data, use UNION ALL, as follows:

Select * from user1 union all select * from user2