1 The Union Operator
The union operator returns results form both queries after eliminating duplications.\
select employee_id,job_id
from employees
uniion
select employ_id ,job_id
from job_history;
2 the all operator
The union all opertor reutrn result from both queries,including all duplications
3 interset
select ster_id,qty from sales where qty>20;
intersect
select ster_id,qty from sales where ster_id like '7%';
4 minus
select ster_id,qty from sales where qty>20
minus
select ster_id from sales where ster_id like '7%'
5 set operator guidelines
. teh expressions in the select list must match in number and data type;
. Parentheses can be used to alter the sequence of the execution
.The order by clause:
can appear only at the very end of the statement
will accept the column name,aliases from thee firest select statement ,or thee positional notation
.Duplicate row are atuomatically eliminated except in union all.
.Column names from the first query appear in the result
.The output is sorted in ascending order by default except in union all
6 matching the select statement
select department_id,to_number(null),location,hire_date
from employees
union
select department_id,location_id,to_date(null)
from departments;
select employee_id,job_id,salary
from employees
union
select employee_id,job_id,0
from job_history;
7 Controlling the order of the rows
select 'sing' as "my dream" ,3,a_dummy
from dual
union
select 'like''d like to teach',1
from dual
union
select 'the world to',2
from dual
order by 2;