[ORACLE] 집합 연산자

이번엔 집합 연산자를 알아 볼 것 이다.

---

  ●집합연산자
  

두 테이블에 대해서 SELECT 문으로 각각 데이터를 조회해서 새로운 데이터로 가공하고 싶을 때 집합 연산자를 사용한다. 집합 연산자를 사용하기 위해서는 두 테이블에서 SELECT 문의 컬럼_명, 컬럼_위치, 컬럼_수가 동일해야한다. 그렇지 않으면 'ORA-01789: 질의 블록은 부정확한 수의 결과 열을 가지고 있습니다'와 같은 에러가 발생한다. 만약 컬럼_명이 같지 않다면 ALIOS를 활용하여 억지로라도 같게 만들어주어야 한다. SELECT 문을 활용한 집합 연산자에는 UNION(합집합), UNION ALL, INTERSECT(교집합), MINUS(차집합)가 있다. 

 

---

●예제

****집합연산자
UNION
select employee_id, salary, department_id from employees
where salary > 10000;

select employee_id, salary, department_id from employees
where salary > 10000;

UNION(합집합)
select employee_id, salary, department_id from employees
where department_id = 100;
<비교>
select employee_id, salary, department_id from employees
where salary > 10000;

select employee_id, salary, department_id from employees
where department_id = 100;
<비교>
select employee_id, salary, department_id from employees
where salary > 10000;

UNION ALL 

교집합까지 나오게 하는것 월급이 10000이상이고 부서도 100번인
select employee_id, salary, department_id from employees
where department_id = 100;
=중복된 데이트가 나온다. 데이터로서 효율이 떨어짐
order by는 제일 밑에 

select employee_id, salary, department_id from employees
where department_id = 100;

MINUS(여집합)
select employee_id, salary, department_id from employees
where salary > 10000;

select employee_id, salary, department_id from employees
where salary > 10000;

MINUS
select employee_id, salary, department_id from employees
where department_id = 100
order by department_id;//100번 부서만 빼고

select employee_id, salary, department_id from employees
where department_id = 100
order by department_id;

INTERSECT(교집합)
select employee_id, salary, department_id from employees
where salary > 10000;

select employee_id, salary, department_id from employees
where salary > 10000;

INTERSECT
select employee_id, salary, department_id from employees
where department_id = 100
order by department_id;

select employee_id, salary, department_id from employees
where department_id = 100
order by department_id;