Merge and sort the records of multiple tables as follows:
Select * from (select * from Table
Union
Select * from Table B
Union
Select * Table c) as h order by h. List desc
In the database tutorial, the union and union all keywords combine the two results into one, but both of them 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.
For example:
Select * from test_union1
Union
Select * from test_union2
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 we can confirm that the two results of the merge do not contain duplicate data, we use union all.