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;
'Oracle > SQL Fundamentals I' 카테고리의 다른 글
10일차 # 7-9: UNION (0) | 2012.04.17 |
---|---|
10일차 # 7-17: Guidelines (0) | 2012.04.17 |
9일차 문제(subquery) (0) | 2012.04.16 |
9일차 # Top-N reporting(analysis) (0) | 2012.04.16 |
9일차 # Pseudocolumn (0) | 2012.04.16 |