The difference between Union and Union All

The 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 keyword. The function of union (or union) is to combine multiple results and display them together.

The difference between union and union all is that union will automatically compress duplicate results in multiple result sets, while union all will display all results, regardless of whether they are duplicates or not.

Union: performs a union operation on two result sets, excluding duplicate rows, and sorts by default rules;

Union All: performs a union operation on two result sets, including duplicate rows, without sorting;

Intersect: performs an intersection operation on two result sets, excluding duplicate rows, and sorts by default rules at the same time;

Minus: performs a difference operation on two result sets, excluding duplicate rows, and sorts by 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 combines the results of the two tables. These two examples will compress the duplicate values ​​in the results of the two select statements, that is, the result data is not the sum of the number of the two results. If you want to display duplicate results, you can use union all, for example:

2. In Oracle's scott user, there is a table emp
select * from emp where deptno >= 20
union all
select * from emp where deptno < ;= 30
The results here have many duplicate values.

Things to note about the union and union all keywords are:

Both union and union all can merge multiple result sets, not just two. You can string multiple result sets together.
When using union and union all, you must ensure that the results of each select set have the same number of columns, and the type of each column is the same. But the column names do not necessarily need to be the same. Oracle will use 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 don’t need to use the order by clause in each select result set to sort. We can use an order by at the end. Sort the entire result. For example:
select empno,ename from emp
union
select deptno,dname from dept
order by ename;