Curso Oracle 11g - Lesson 8 - Programação

-- Lesson 8
--Set Operator
--Union / Union ALL

--União de tabelas

Select employee_id,
       Case when (select max(end_date) from job_history where employee_id = e.employee_id) is null
            then hire_date
            else (select max(end_date + 1) from job_history where employee_id = e.employee_id)
       end As Start_Date,
       null as end_date,
       job_id, department_id
from employees e
union
select employee_id,
       Start_date,
       end_date,
       job_id, department_id
from job_history
Order by employee_id, start_date desc;


select employee_id, job_id --107 linhas
from employees;
select employee_id, job_id --10 linhas
from job_history;

select employee_id, job_id, 'atual'
from employees
union
select employee_id, job_id, 'historico'
from job_history; --115 linhas

select employee_id, job_id, 'atual'
from employees
union all
select employee_id, job_id, 'historico'
from job_history; --117 linhas


--descrever a estrutura da tabela
desc regions;
desc locations;

--mesmo com tamanhos de campos diferente, é possivel unir as tabelas
Select region_name
From regions
union
select city
from locations;


--Intersect
--traz somente o que tiver em ambas as tabelas
select employee_id, job_id, last_name ||', '|| first_name name
from employees
intersect
select j.employee_id, j.job_id, last_name ||', '|| first_name name
from job_history j join employees e
                        on j.employee_id = e.employee_id
order by employee_id, 2;


--Minus
--traz da primeira consulta, o que não tiver na segunda consulta
select employee_id, job_id, last_name ||', '|| first_name name
from employees
minus
select j.employee_id, j.job_id, last_name ||', '|| first_name name
from job_history j join employees e
                        on j.employee_id = e.employee_id
order by employee_id, 2;

--mesma coisa que o Minus usando join
select e.employee_id
from employees e left join job_history j
                   on e.employee_id = j.employee_id
where j.employee_id is null;                  

-- mesma coisa que o Minus usando subconsulta
select employee_id
from employees
where employee_id not in (select employee_id from job_history);

Fiquem a vontade para comentar e sugerir melhorias e/ou correção dos exemplos acima.