[SQL] SET OPERATOR

21 Jul 2018

Reading time ~3 minutes

SET OPERATOR (집합 연산자)

합집합
교집합 (EQUI JOIN/EXISTS의 개념)
차집합 (NON EXISTS의 개념)

집합연산자 사용시 주의해야할 점

집합 연산자는 쿼리문장의 갯수와 타입을 일치시켜야 한다. (컬럼 이름은 달라도 상관 없음)
집합들을 여러개 쓸 수 있다. 하지만 괄호를 사용해 잘 묶어서 쓰자
ORDER BY 절을 사용한다면 제일 마지막 줄에 쓰고, 첫번째 쿼리문장의 표현식을 써야 한다. (편하게 위치 표기법을 쓰자)
하지만! 집합연산자는 사용하지 않으면 않을수록 좋다!
특히 UNION, INTERSECT, MINUS를 사용하면 내부적으로 SORT OPERATOR가 발생되기 때문에 사용하지말자!
- UNION은 UNION ALL + NOT EXISTS
- INTERSECT는 EXISTS
- MINUS는 NOT EXISTS 로 바꿔서 사용하자

각각의 쿼리문을 따로 실행계획을 만들 때 사용되는 것 또한 확인하자.

합집합

UNION과 UNION ALL

SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;

UNION은 중복되는 것을 제거해서 합치는데 그 방법이 SORT. 그래서 오버헤드가 크게 발생 수 있다.

SELECT employee_id, job_id
FROM employees
UNION ALL
SELECT employee_id, job_id
FROM job_history;

UNION ALL은 SORT를 하지 않아 성능은 좋아질수 있으나, SORT를 사용하지 않아서 중복된 값이 생긴다. 그래서 중복된 값을 없애기 위해 UNION ALL과 NOT EXISTS를 사용하여 UNION의 결과를 만들자

SELECT employee_id, job_id
FROM employees
UNION ALL
SELECT employee_id, job_id
FROM job_history j
WHERE NOT EXISTS (
  SELECT 'X'
  FROM employees
  WHERE employee_id = j.employee_id
  AND job_id = j.job_id);

참고. 새로운 컬럼을 만들기위해서는 기준 컬럼과 같은 타입의 상수값을 쓰면 된다.

SELECT employee_id, job_id, salary
FROM employees
UNION ALL
SELECT employee_id, job_id, null --숫자타입의 상수값을 집어넣어도됌(문자는 안됌)
FROM job_history;

교집합

INTERSECT 와 EXISTS

SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;

위 쿼리 또한 SORT OPERATOR가 발생한다. 만약 데이터가 많으면 부하가 심해진다. 그래서 EXISTS를 사용하자!

SELECT employee_id, job_id
FROM employees e
WHERE EXISTS (
  SELECT 'X'
  FROM job_history
  WHERE employee_id = e.employee_id
  AND job_id = e.job_id);

차집합

MINUS 와 NOT EXISTS

SELECT employee_id, job_id
FROM employees
MINUS
SELECT employee_id, job_id
FROM job_history;

얘도 SORT가 발생. 그래서 MINUS대신에 NOT EXISTS를 사용해여 문제를 해결하자

SELECT employee_id, job_id
FROM employees e
WHERE NOT EXISTS (
  SELECT 'X'
  FROM job_history
  WHERE employee_id = e.employee_id
  AND job_id = e.job_id);

GROUP BY절과 집합 연산자

ROLLUP 연산자

GROUP BY 절에서 지정한 열 리스트를 오른쪽에서 왼쪽으로 한 방향으로 이동하여 그룹화를 만든다.

/*
[문제86] 
1. department_id, job_id, manager_id 기준으로 총액 급여를 출력
2. department_id, job_id 기준으로 총액급여출력
3. department_id 기준으로 총액급여 출력
4. 전체 총액 급여를 출력

1,2,3,4를 한꺼번에 출력해주세요.
*/


SELECT department_id, job_id, manager_id, sum(salary)
FROM employees
GROUP BY department_id, job_id, manager_id
UNION ALL 
SELECT department_id, job_id, null, sum(salary)
FROM employees
GROUP BY department_id, job_id
UNION ALL
SELECT department_id, null, null, sum(salary)
FROM employees
GROUP BY department_id
UNION ALL
SELECT null,null, null, sum(salary)
FROM employees;

employees 테이블을 4번을 호출하게되어서 부하가 심함. 그럴 때 테이블을 하나만 부를 수 있는 ROLLUP 연산자를 사용하자.

SELECT department_id, job_id, manager_id, sum(salary)
FROM employees
GROUP BY ROLLUP(department_id, job_id, manager_id);

CUBE 연산자

GROUP BY 절에 지정된 가능한 모든 그룹화를 한다.

SELECT department_id, job_id, manager_id, sum(salary)
FROM employees
GROUP BY CUBE(department_id, job_id, manager_id);

ROLLUP 연산자와 CUBE 연산자의 차이

GROUP BY ROLLUP(A,B,C)

SUM(sal) = {A,B,C}
SUM(sal) = {A,B}
SUM(sal) = {A}
SUM(sal) = {}

GROUP BY CUBE(A,B,C)

SUM(sal) = {A,B,C}
SUM(sal) = {A,B}
SUM(sal) = {A,C}
SUM(sal) = {B,C}
SUM(sal) = {A}
SUM(sal) = {B}
SUM(sal) = {C}
SUM(sal) = {}

ROLLUP은 사용할 수 없고 CUBE는 필요없는 값까지 출력될 수 있으니, 주의해서 사용하자.
그럼 내가 직접 원하는 그룹을 만들려고 할 때 사용할 수 있는 연산자는 없을까? 있다.

GROUPING SETS 연산자

내가 직접 원하는 그룹을 만들 때 사용하는 연산자. 문제 87번을 바꾸어 보면 다음과 같다.

SELECT department_id, manager_id, null, SUM(salary)
FROM employees
GROUP BY department_id,manager_id
UNION ALL
SELECT department_id, null, job_id, SUM(salary)
FROM employees
GROUP BY department_id,job_id
UNION ALL
SELECT null, null, null, SUM(salary)
FROM employees;

--ROLLUP과 CUBE 둘다 사용 못함

GROUPING SETS을 사용하여 department_id, manager_id / department_id, job / 전체의 SUM(salary) 값을 구해보자.

SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department_id,manager_id),
                       (department_id,job_id),
                       ();

sql, setoperator sql