제2절 집합 연산자(SET OPERATOR)
집한 연산자
- 두개 이상의 연관된 테이블에서 데이터를 조회하는 방법
- 여러개의 질의의 결과를 연결하여 하나로 결합
- 튜닝관점에서 실행계획을 분리하고자 할때
--UNION
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID=10
UNION
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID=20
ORDER BY LAST_NAME;
--같은 컬럼에 대한 IN연산자 대체 UNION 효과
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10, 20);
UNION ALL
UNION ALL은 UNION과 다르게 집합내에서 중복이 생길수 있음(단순 결합)
--UNION ALL
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10, 20)
UNION ALL
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10, 20)
ORDER BY LAST_NAME;
--MINUS
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10, 20)
MINUS
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID =10
ORDER BY LAST_NAME;
--NOT IN를 이용한 MINUS효과
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES A
WHERE DEPARTMENT_ID IN (10, 20)
AND EMPLOYEE_ID NOT IN
(SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 10);
--NOT IN를 이용한 MINUS효과
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES A
WHERE DEPARTMENT_ID IN (10, 20)
INTERSECT
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES A
WHERE DEPARTMENT_ID = 10;
--IN연산자를 이용한 INTERSECT효과
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES A
WHERE DEPARTMENT_ID IN (10, 20)
AND EMPLOYEE_ID IN
(SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 10);
