Union and UNION ALL differences

Union is inefficient because it is being scanned for duplicate values. If the merge does not intentionally delete duplicate rows, then use the Union AllTwo 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 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: The two result sets are combined, excluding duplicate rows, while the default rules are sorted;Union  All: Perform a set operation on two result sets, including repeating rows, without sorting;Intersect: The intersection of two result sets, excluding duplicate rows, and the sorting of 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:Selectemployee_id,job_id fromEmployeesUnion Selectemployee_id,job_id fromJob_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 to be used even if repeated results are displayed All, such as:2. There is a table emp in the Scott user of OracleSelect *  fromEmpwhereDeptno>=  - Union  All Select *  fromEmpwhereDeptno<=  -The result here is a lot of duplicate values. Questions to note about the Union and UNION ALL keywords are:UnionAndUnionall 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:SelectEmpno,ename fromEMPUnion SelectDeptno,dname fromDept We do not need to use the ORDER BY clause in each of the select result sets to sort by, we can use an order by at the end to sort the entire result. For example:SelectEmpno,ename fromEMPUnion SelectDeptno,dname fromDeptOrder  byename;

Union and UNION ALL differences