ORACLE

오라클 4일

[SET operator]-집합연산자

두개 이상의 쿼리결과를 하나로 결합시키는 연산자

• UNION : 양쪽쿼리를 모두 포함(중복 결과는 1번만 포함) –> 합집합
• UNION ALL : 양쪽쿼리를 모두 포함(중복 결과도 모두 포함)
• INTERSECT : 양쪽쿼리 결과에 모두 포함되는 행만 표현 –> 교집합
• MINUS : 쿼리1결과에 포함되고 쿼리2결과에는 포함되지 않는 행만 표현 –>차집합

오라클의 집합연산자(SET operator) UNION, INTERSECT, MINUS 는 order by 한다

• 컬럼이 많으면 order by 하므로 느려진다. 수가 작은 튜플로 가공 후 사용하는게 좋다

• UNION ALL 는 order by 하지 않고 무조건 합해준다

  Order by를 하려면 두번째 쿼리에 작성해야 한다

[연습용 테이블]

create table employees_role as select * from employees where 1=0; ← 테이블 구조만 복사

select * from employees_role;

insert into employees_role values(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568',
'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568',
'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(101, 'Nee', 'Ko', 'NKOCHHAR', '515.123.4568',
'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(200, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568',
'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(200, 'Nee', 'Kochhar', 'NKOCHHAR', '515.123.4568',
'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(300, 'GilDong', 'Conan', 'CONAN', '010-123-4567',
'2009-03-01', 'IT_PROG', 23000.00, NULL, 100, 90);

ex1 ) union

employee_id, last_name이 같을경우 중복제거 하시오 → 110 레코드

select employee_id, last_name from employees
union
select employee_id, last_name from employees_role;

ex2 ) union all

employee_id, last_name이 같을경우 중복을 허용 하시오 → 113 레코드

select employee_id, last_name from employees
union all
select employee_id, last_name from employees_role;

select salary from employees where department_id=10
union all
select salary from employees where department_id=30 order by 1;

ex3 ) minus

employees_role과 중복되는 레코드는 제거하고 employees에만 있는 사원명단을 구하시오 (단, employee_id, last_name만 표시) → 106 레코드

select employee_id, last_name from employees
minus
select employee_id, last_name from employees_role;

ex4 ) intersect

employees와 employees_role에서 중복되는 레코드의 사원명단을 구하시오 (단, employee_id, last_name만 표시) → 1 레코드

select employee_id, last_name from employees
intersect
select employee_id, last_name from employees_role;

[문제 1] employees와 employees_role에서 레코드의 사원명단을 구하시오

조건1) 사원이름, 업무ID, 부서ID을 표시하시오

조건2) employees 에서는 부서ID가 10인사원만

employees_role에서는 업무ID가 IT_PROG만 검색

조건3) 중복되는 레코드는 제거

ex5) SET operator과 IN operator관계

job_title이 ‘Stock Manager’ 또는 ‘Programmer’인 사원들의 사원명과 job_title을 표시하시오

last_name    job_title

------------------------

Kaufling    StockManager

Hunlod     	Programmer

	      :

방법1 (join, in연산자 이용)

select last_name, job_title
from employees
join jobs using(job_id)
where job_title in('Stock Manager','Programmer');

방법2 (join, union 이용)

select last_name, job_title
from employees
join jobs using(job_id)
where job_title='Stock Manager'

union

select last_name, job_title
from employees
join jobs using(job_id)
where job_title='Programmer'
order by 2;

ex9 ) 컬럼명이 다른경우의 SET operator

쿼리1과 쿼리2의 select 목록은 반드시동일(컬럼갯수,데이터타입)해야 하므로 이를 위해 Dummy Column을 사용할수 있다.

select last_name, employee_id, hire_date
from employees
where department_id=20

union

select department_name, department_id, NULL
from departments
where department_id=20;