The difference between intersect,minus,union all and union

Union is inefficient because it is scanned for duplicate values. If the merge does not intentionally delete duplicate rows, then use UNION ALL

Two the number of SQL statement fields to be joined must be the same, and the field type should be "compatible" (consistent);

If we need to display the results of the two SELECT statements as a whole, we need the union or UNION ALL keyword. The role of a Union (or union) is to combine multiple results together to display them.

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


Union: The two result sets are combined to set operations, excluding duplicate rows, while the default rules are sorted;

Union all: A set of two result sets, including duplicate rows, is not sorted;

Intersect: Intersection of two result sets, excluding duplicate rows, and ordering of default rules;

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

You can specify an ORDER BY clause in the last result set to change the sort method.

For example:

Select employee_id,job_id from Employees
Union
Select employee_id,job_id from Job_history

The results of the two tables are combined together. These two examples compress the duplicates in the results of the two SELECT statements, that is, the data of the result is not the same as the number of bars of two results. If you want to use union all even if the results are repeated, for example:

2. There is a table emp in the Scott user of Oracle
SELECT * from emp where deptno >= 20
UNION ALL
SELECT * from emp where Deptno <= 30
The results here have a lot of duplicate values.

The issues 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. However, the column name does not necessarily need to be the same, and Oracle takes the column name of the first result as the column name for the result set. For example, here is an example:
Select Empno,ename from emp
Union
Select Deptno,dname from Dept
We don't need to sort by using an ORDER BY clause in each select result set, and we can sort the entire result by using an order by. For example:
Select Empno,ename from emp
Union
Select Deptno,dname from Dept
Order by ename;