Difference between Union and Union all

A view is created based on three 10 million rows. Table links are implemented by Union. The following is an example of SQL:

 

Create view emaillogview
With schemabinding
As
Select datatime, username, sourceip, destip, emailfrom, emailto, emailcc, emailbcc, emailsub, emaildate, orderid from DBO. emaillog

Union

Select datatime, username, sourceip,
Destip, emailfrom, emailto, emailcc, emailbcc, emailsub, emaildate,
Orderid from DBO. emaillog1

Union

Select datatime, username, sourceip,
Destip, emailfrom, emailto, emailcc, emailbcc, emailsub, emaildate,
Orderid from DBO. emaillog2

 

The query view through select is found to be super slow, but the query for a single base table is found to be relatively fast. The query for half a day is found to be a problem with the use of Union, by default, Union sorts the data, removes duplicates, and sorts tens of millions of data at a predictable speed... The speed of changing to Union all is significantly improved.

 

The following describes the differences between Union and Union all:

 

 

In the database, union and Union
The All keyword combines two results into one, but the two 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 gc_dfys union select * from ls_jg_dfys

 

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.

 

While 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
It is much faster than union, so if you can confirm that the two results of the merge do not contain duplicate data, then use Union all, as shown below:

 

select * from gc_dfys union all select * from ls_jg_dfys