SQL Server Functions (iii) -- difference between Union and Union all

If we need to display the results of two select statements as a whole, we need to use the Union or union all keywords. Union (or union) is used to display multiple results together.

The difference between Union and Union all is that union will automatically compress the repeated results in multiple result sets, while union all will display all the results, whether they are repeated or not.

Union: Perform Union operations on two result sets, excluding duplicate rows and sorting by default rules;

UnionDuplicate records are filtered out after the table link is established. Therefore, after the table link is established, the generated result set is sorted, and duplicate records are deleted before the results are returned.In most applications, duplicate records are not generated. The most common is Process Tables and historical tables.Union

 

 

 

 

Union all: Perform Union operations on two result sets, including duplicate rows without sorting;

If duplicate data exists in the two returned result sets, the returned result sets will contain duplicate data.

Intersect: intersection of two result sets, excluding duplicate rows, and sorting by default rules;

Minus: performs the Difference Operation on two result sets, excluding duplicate rows, and sorts the default rules at the same time.

You can specify the order by clause in the last result set to change the sorting method.

For example:

Select employee_id, job_id from employees
Union
Select employee_id, job_id from job_history

The above two tables are joined together. The two examples compress the duplicate values in the results of the two select statements, that is, the result data is not the sum of the two results. If you want to use Union all even if repeated results are displayed, for example:

2. The Scott user in Oracle has the table EMP.
Select * from EMP where deptno> = 20
Union all
Select * from EMP where deptno <= 30
The results here have a lot of repeated values.

Note the following when using the Union and Union all keywords:

Union and Union all can combine multiple result sets, not just two. You can concatenate multiple result sets.
When using Union and Union all, you must ensure that the results of each select set have the same number of columns, and each column has the same type. But the column names do not need to be the same. Oracle uses the column name of the first result as the column name of the result set. For example, the following is an example:
Select empno, ename from EMP
Union
Select deptno, dname from Dept
We do not need to use the order by clause in each select result set for sorting. We can use an order by clause to sort the entire result at the end. For example:
Select empno, ename from EMP
Union
Select deptno, dname from Dept
Order by ename; this documentArticleSource: Institute of Development http://edu.codepub.com Source: http://edu.codepub.com/2009/0803/12019.php