[5일차]set 연산자

# hr 로그인

grant select on JOB_HISTORY to public;

#aa01 로그인 하여 작업

create table JOB_HISTORY
as
select * from hr.JOB_HISTORY;

## union
-쿼리 결과를 위아래로 합치는 것
-두 쿼리문의 스키마가 같아야 한다.(컬럼의 개수, 타입이 같아야 한다.)
-첫번째 쿼리문의 컬럼명이 대표 컬럼명이 된다.
=중복 불가:한번만!!!
=첫번째 컬럼값을 기준으로 오름차순으로 정렬

## union all
-쿼리 결과를 위아래로 합치는 것
-두 쿼리문의 스키마가 같아야 한다.(컬럼의 개수, 타입이 같아야 한다.)
-첫번째 쿼리문의 컬럼명이 대표 컬럼명이 된다.
=중복 허용!!!
=정렬 안됨!!!

select last_name, salary,department_id
from emp
where department_id = 30
union
select last_name, salary
from emp
where department_id = 60;

select employee_id,job_id
from emp
union all
select employee_id,job_id
from job_history;

--현재 종사중인 직종의 과거에도 종사한 경험이 있는 직원 검색
select last_name, job_id
from emp
intersect
select last_name,job_id
from job_history;

--직종을 한번도 변경하지 않은 직원 검색
select last_name,

--현재직종 - 과거직종
--현재직종 - 없음 => 직종을 변경하지 않은 직원
select last_name, job_id
from emp
minus
select last_name,job_id
from job_history;

## set 연산자 사용지침
## 스키마가 안 맞는 경우 => 유니온 하는 방법

select department_id,to_number(null) as location,hire_date
from emp
union
select department_id,location_id,to_date(null)
from dept;

select department_id, job_id, salary
from emp
union
select department_id,job_id,0
from job_history;