The difference between union and unions all in Oracle

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 them.

The difference between Union and union all is that union automatically compresses duplicate results in multiple result sets, and union ALL displays all results, whether duplicates or not.

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:

Select employee_id,job_id from Employees

Union

Select employee_id,job_id from Job_history

selectemployee_id,job_id from Employees

Union

selectemployee_id,job_id from Job_history

The results of the two tables are joined together.

These two examples will be two

Select

The duplicate values in the result of the statement are compressed,

That is, the data of the result is not the same as the number of two-result bars. If you want to be able to use even if the duplicate results appear

UNION ALL,

For example:

2. There is a table EMP code 1 in the Scott user of Oracle. SELECT * from emp where Deptno >= 20 2. UNION ALL 3. SELECT * from emp where Deptno <=30 select * from EMP where deptno>= UNION ALL SELECT * from EMP where deptno< ; = 30

The result here is a lot of duplicate values.

The problem with the Union and UNION ALL keywords is that the Union and union all can combine multiple result sets, not just two, and you can string together multiple result sets.

The difference between union and unions all in Oracle