The difference between Union and union all in database _ database other

This evening, we found two differences in the operation of two tables. Oh.

Union joins two tables and deletes their duplicates;

UNION ALL joins the two tables without deleting their duplicates.

This stuff is very simple. But also record a ha. It's a small harvest.

Supplementary information:

In the database, Union and UNION all combine two result sets into one, but both are different in terms of usage and efficiency.

The Union filters out duplicate records after a table link, so the resulting result sets are sorted after the table is connected, the duplicate records are deleted, and the results are returned. Most applications do not produce duplicate records, the most common of which is the process table and the History table Union. Such as:

SELECT * FROM users1 Union SELECT * User2
This SQL is run with the result of two tables first, then sorted by sorting to delete duplicate records, and finally return the result set, if the table data volume may cause the disk to sort.

The Union all simply merges the two results and returns. Thus, if the returned two result sets have duplicate data, 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 merged two result sets do not contain duplicate data, then use Union ALL, as follows:

SELECT * FROM User1 UNION ALL select * from User2