"Go" Oracle Set operation functions: Union, intersect, minus

The set operator is specifically used to combine the results of multiple SELECT statements, including: Union, union All, INTERSECT, minus. When you use the SET operator, you must ensure that the number of columns in different queries matches the data type.

The collection operator has the following considerations:

• Collection operators are not available for LOB, Varray, and nested table columns.
• The UNION, INTERSECT, minus operators are not used in long columns.
• If the selection list contains an expression or function, you must define a column alias for the expression or function.

1. Union (no counterweight): When the union is executed, the duplicate rows in the result set are automatically removed and sorted in ascending order with the results of the first column.

2, UNION all (with heavy unions): do not remove duplicate rows, and do not sort the result set.

3, INTERSECT (intersection): take the intersection of two result sets, and the results of the first column in ascending order.

Select Id,name,job from Worker
INTERSECT
Select Empno,ename,job fromemp;

4, minus (difference set): displays only data that exists in the first collection that does not exist in the second collection. And the results of the first column are sorted in ascending order.

5. Alternatively, you can use ORDER by

The order by must be placed after the last SELECT statement, and when the column name is the same, it can be sorted directly with the column name, or the alias can be used if the difference can be sorted by location.

Select ID, name x from New_emp
UNION ALL
Select Empno, ename x from emp ORDER by x;//column names are not used with alias ordering

Select ID, name ename from New_emp
UNION ALL
Select Empno, ename from emp ORDER by ename;//column names do not use aliases at the same time to sort after

Select ID, name ename from New_emp
UNION ALL
Select Empno,ename from EMP; // Merge column name displays the previous table as the primary.

"Go" Oracle Set operation functions: Union, intersect, minus