What is the difference between union and union all in the database

This article will explain in detail what is the difference between union and union all in the database. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Union deletes duplicates after joining two tables

Union all joins both tables without removing their duplicates.

This thing is very simple. But also record it. It's really a small gain.

Supplementary information:

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

On the database of what is the difference between union and union all to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.