Difference between union and union all in the database

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.