Differences in the Union and Union All in Oracle (for multiple databases)

differences in the Union and Union All in Oracle (for multiple databases)

If we need to display the results of the two SELECT statements as a whole, we need to use the union or UNION ALL keyword. Union (or union) is the function of merging multiple results together to display

The Union and union all are combined to merge the query result set of SELECT, so what is the difference?

The union then re-checks the query into the result set and removes the same rows. Disadvantage: low efficiency;
The union all is simply a result set of the merge query and is not re-queried, but it is highly efficient, but redundant data may appear.

Union: Set operation on two result sets, excluding duplicate rows, and sorting the default rules;

Union All: Set operation on two result sets, including repeating rows, without sorting;

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

Minus: Perform a differential operation on two result sets, excluding duplicate rows, and sorting the default rules.

The ORDER BY clause can be specified in the last result set to change the ordering method.

For example:

Copy CodeThe code is as follows:
Select employee_id,job_id from Employees
Union
Select employee_id,job_id from Job_history

The results of the two tables are joined together. These two examples compress the duplicate values in the results of the two SELECT statements, which means that the data for the result is not the same as the number of the two result bars. If you want the union all to be used even if repeated results are displayed, for example:

2. There is a table emp in the Scott user of Oracle

Copy CodeThe code is as follows:
SELECT * from emp where deptno >= 20
UNION ALL
SELECT * from emp where Deptno <= 30

The result here is a lot of duplicate values.

Questions to note about the Union and UNION ALL keywords are:

Union and UNION ALL can combine multiple result sets, not just two, and you can string together multiple result sets.
using union and union all must ensure that the results of each select collection have the same number of columns, and that each column is of the same type. But the column names do not necessarily need to be the same, and Oracle will use the column name of the first result as the column name for the result set. For example, here is an example:

The code is as follows:
Select Empno,ename from emp
Union
Select Deptno,dname from Dept

We do not need to use the ORDER BY clause in each of the select result sets to sort by, and we can use an order by at the end to sort the entire result. For example:

Copy CodeThe code is as follows:

Select Empno,ename from emp
Union
Select Deptno,dname from Dept
Order by ename;

Turn from: http://www.jb51.net/article/30792.htm