The difference between MySQL union and union all and the use

Union is inefficient because it is being 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 federate must be the same, and the field type is "compatible" (consistent);

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

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
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:
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:
Select Empno,ename from emp
Union
Select Deptno,dname from Dept
Order by ename;

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

The difference between MySQL union and union all and the use