Oracle collection Query

Union
Select * from emp where deptno = 10 union select * from emp where deptno = 20;
Select * from emp where deptno = 10 union all select * from emp where deptno = 20;
Select * from emp where ename like '% A %' or ename like '% M % ';
Select * from emp where ename like '% A %' union ename like '% M %'; // remove all parts
Select * from emp where ename like '% A %' union all ename like '% M %'; // do not remove
Intersection
Select * from emp where ename like '% A %' intersect ename like '% M %'; // common parts
Difference set
Belongs to first, not belongs to second
The Order by clause can only appear in the last statement. The column name or alias accepted by the first select statement
The column name of the query result must be the same as the column name of the first select statement.
Select * from (select * from emp where sal between 700 and 1300 union select * from emp where sal between 1200 and 1400) order by sal desc;
Select ename, sal from emp where sal between 700 adn 1300 union select ename, sal from emp where sal between 1200 and 1400 order by sal deces;
In the select statement, the parameter types and numbers must be consistent.
Brackets can be used to change the sequence of execution of the set.
The set operation uses the header of the first statement as the header.


From Zhang Xiaocong's column