10일차 7 집합연산자 Set Operators(using the set operators)

UNION Operator

아무런 상관관계가 없는 테이블을 위아래로 붙히는것
열숫자가 같아야하고 데이터타입이 같아야함

select
employee_id,job_id
from employees
union                    <--중복제거 all 넣으면 중복허용  UNION all
select
employee_id,job_id
from job_history
/

INTERSECT Operator

select
employee_id, job_id
from employees
intersect
select
employee_id, job_id
from job_history
/

MINUS Operator

select
employee_id, job_id
from employees
minus
select
employee_id, job_id
from job_history
/

--------------  열을맞추기위해 데이터타입이같은 null값열을 만들어 이용할때
select
department_id
,to_number(null) location
,hire_date
from employees
union
select
department_id
,location_id
,to_date(null)
from departments
/

------------------ 유격이안맞을때  0을이용해서 붙힐때
select
employee_id
,job_id
,salary
from employees
union
select 
employee_id
,job_id
,0
from job_history
/

----------------------- 유니온 2개 3개를 합치는것

COLUMN a_dummy NOPRINT -> a_dummy 열을 출력하지말아라.
SELECT 'sing' AS "My dream", 3 a_dummy
FROM dual
UNION
SELECT 'I''d like to teach', 1 a_dummy
FROM dual
UNION
SELECT 'the world to', 2 a_dummy
FROM dual
ORDER BY a_dummy;