Union and UNION all and other set operation instructions

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.

Let's give an example to illustrate:

For example, there are two tables Tab1 and TAB2 in the database.

The data in the TAB1 are:

Xiao Wang

Xiao Zhang

The data in the TAB2 are:

Xiao Wang

Xiao Li

To execute a query:

The code is as follows: SELECT * FROM TAB1 UNION select * from TAB2

The results are as follows:

Xiao Wang

Xiao Zhang

Xiao Li

If you perform the following query:

The code is as follows:
SELECT * from Tab1 UNION all SELECT * from TAB2

The result is as follows:

Xiao Wang

Xiao Zhang

Xiao Wang

Xiao Li

Does this look different?

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:

The 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

The 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:

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

Union and UNION all and other set operation instructions