집합 (Set)
🙋♀️ select 명령문으로 출력되는 결과 행들을 집합으로 볼 수 있다!
1. 집합 연산자 (Set Operator)
집합 연산을 하려면
-
SELECT 리스트에 있는 컬럼 개수가 일치해야 한다.
- 안 맞으면 임의로 literal 컬럼 만들어서 일치시켜야 한다.
-
ORDER BY 절은 명령문 맨 끝에만 올 수 있다.
-
첫번째 쿼리의 열 이름이 결과에 나타난다.
1) UNION ALL
- 중복항목 제거되지 않는다.
- 결과 정렬되지 않는다.
- 각 쿼리를 그대로 붙이기만 해서 출력된다.
💡 UNION ALL 연산자 활용해서 이런 식으로 출력할 수 있다.
SELECT NULL AS 구분, deptno, job, SUM(sal) AS total_sal
FROM emp
GROUP BY deptno, job -- 부서별, 직무별 연봉합
UNION ALL
SELECT '소계', deptno, NULL, SUM(sal)
FROM emp
GROUP BY deptno -- 부서별 연봉합
UNION ALL
SELECT '총계', NULL, NULL, SUM(sal)
FROM emp -- 전체 연봉합
ORDER BY 2,3;결과
구분 DEPTNO JOB TOTAL_SAL
----- ------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
소계 10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
소계 20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
소계 30 9400
총계 29025
13개 행이 선택되었습니다. 2) UNION
- 합집합 개념
- 두 쿼리 결과를 합한 후 중복 제거, 정렬해서 출력한다.
3) INTERSECT
- 교집합 개념
- 두 쿼리 결과에 중복되는 값들을 정렬해서 출력한다.
예) 매니저역할 하는 사원 찾기
--사원번호 컬럼과, 매니저 컬럼에 둘다 존재하는 행들을 찾는다
SELECT empno
FROM emp
INTERSECT
SELECT mgr
FROM emp; 4) MINUS
- 차집합 개념
예) 사원이 근무하지 않는 부서 찾기
-- dept(부서) 테이블의 부서번호 컬럼에서, emp(사원정보) 테이블에 존재하는 부서번호 컬럼을 뺀다
SELECT deptno
FROM dept
minus
SELECT deptno
FROM emp;예2) 1~100까지 수 중 랜덤으로 7개 삭제하고, 삭제된 수가 무엇인지 알아보자
-- 1 ~ 100까지 생성
CREATE TABLE t1
AS
SELECT LEVEL AS no
FROM DUAL
CONNECT BY LEVEL <= 100;
-- 랜덤으로 7개 수 삭제
DELETE FROM t1
WHERE no in (SELECT ROUND(dbms_random.value(1, 100))
FROM DUAL
CONNECT BY LEVEL <= 7);
-- 7개숫자는 무엇일까요?
SELECT LEVEL AS no
FROM DUAL
CONNECT BY LEVEL <= 100
MINUS
SELECT *
FROM t1;2. 그룹 함수 - ROLLUP & CUBE
GROUP BY 절과 함께 사용되어서 그룹의 추가적인 정보를 제공할 수 있다.
위의 UNION ALL 예제에서 노가다로 출력했던 결과를 이 함수들을 사용해 쉽게 출력 가능하다.
1) ROLLUP
그룹핑하는 컬럼의 소계와 총계 정보를 조회
예) 부서별, 입사년도별, 직무별 연봉합계를 구해보자
SELECT deptno, to_char(hiredate, 'yyyy') AS hire, job, SUM(sal)
FROM emp
GROUP BY deptno, to_char(hiredate, 'yyyy'), job
ORDER BY 1,2,3 ;그냥 GROUP BY 하면 이런 결과가 나온다.
DEPTNO HIRE JOB SUM(SAL)
------ ---- ----------- --------
10 1981 MANAGER 2450
10 1981 PRESIDENT 5000
10 1982 CLERK 1300
20 1980 CLERK 800
20 1981 ANALYST 3000
20 1981 MANAGER 2975
20 1982 ANALYST 3000
20 1983 CLERK 1100
30 1981 CLERK 950
30 1981 MANAGER 2850
30 1981 SALESMAN 5600
11개 행이 선택되었습니다. ROLLUP 함수를 사용해보자.
SELECT deptno, to_char(hiredate, 'yyyy') AS hire, job, SUM(sal)
FROM emp
GROUP BY ROLLUP(deptno, to_char(hiredate, 'yyyy'), job)
ORDER BY 1,2,3;
DEPTNO HIRE JOB SUM(SAL)
------ ---- ----------- --------
10 1981 MANAGER 2450 --- (1)
10 1981 PRESIDENT 5000
10 1981 7450 --- (2)
10 1982 CLERK 1300
10 1982 1300
10 8750 --- (3)
20 1980 CLERK 800
20 1980 800
20 1981 ANALYST 3000
20 1981 MANAGER 2975
20 1981 5975
20 1982 ANALYST 3000
20 1982 3000
20 1983 CLERK 1100
20 1983 1100
20 10875
30 1981 CLERK 950
30 1981 MANAGER 2850
30 1981 SALESMAN 5600
30 1981 9400
30 9400
29025 --- (4)
22개 행이 선택되었습니다.(1) : deptno, hire, job별 연봉합계,
(2) : deptno, hire별 연봉합계,
(3) : deptno별 연봉합계,
(4) : 전체 연봉합계
이렇게 ROLLUP 함수 내에 n개(위 예에서 detpno, hire, job 3개)의 컬럼을 넣으면 n+1개의 결과 그룹이 출력되는 것을 알 수 있다.
💡
- ROLLUP 함수 내에 컬럼 순서를 달리하면 결과도 달라진다.
SELECT deptno, to_char(hiredate, 'yyyy') AS hire, job, SUM(sal)
FROM emp
GROUP BY ROLLUP(job, to_char(hiredate, 'yyyy'), deptno);DEPTNO HIRE JOB SUM(SAL)
------ ---- ----------- --------
20 1980 CLERK 800 --- (1)
1980 CLERK 800 --- (2)
30 1981 CLERK 950
1981 CLERK 950
10 1982 CLERK 1300
1982 CLERK 1300
20 1983 CLERK 1100
1983 CLERK 1100
CLERK 4150 --- (3)
20 1981 ANALYST 3000
1981 ANALYST 3000
20 1982 ANALYST 3000
1982 ANALYST 3000
ANALYST 6000
10 1981 MANAGER 2450
20 1981 MANAGER 2975
30 1981 MANAGER 2850
1981 MANAGER 8275
MANAGER 8275
30 1981 SALESMAN 5600
1981 SALESMAN 5600
SALESMAN 5600
10 1981 PRESIDENT 5000
1981 PRESIDENT 5000
PRESIDENT 5000
29025 --- (4)
26개 행이 선택되었습니다. (1) : job, hire, deptno별 연봉 합계
(2) : job, hire별 연봉 합계
(3) : job별 연봉 합계
(4) : 전체 연봉 합계
ROLLUP 함수 내에 들어간 컬럼 오른쪽 순서대로 하나씩 제외되며 소계를 구해준다.
💡
- ROLLUP 함수 내에 컬럼끼리 묶어서 결과를 출력할 수도 있다.
바로 위 예제에서 굳이 job별 총계는 보고싶지 않다면 어떻게 할까?
SELECT deptno, to_char(hiredate, 'yyyy') AS hire, job, SUM(sal)
FROM emp
GROUP BY ROLLUP( (job, to_char(hiredate, 'yyyy') ), deptno);DEPTNO HIRE JOB SUM(SAL)
------ ---- ----------- --------
20 1980 CLERK 800 --- (1)
1980 CLERK 800 --- (2)
30 1981 CLERK 950
1981 CLERK 950
10 1982 CLERK 1300
1982 CLERK 1300
20 1983 CLERK 1100
1983 CLERK 1100
20 1981 ANALYST 3000
1981 ANALYST 3000
20 1982 ANALYST 3000
1982 ANALYST 3000
10 1981 MANAGER 2450
20 1981 MANAGER 2975
30 1981 MANAGER 2850
1981 MANAGER 8275
30 1981 SALESMAN 5600
1981 SALESMAN 5600
10 1981 PRESIDENT 5000
1981 PRESIDENT 5000
29025 --- (3)
21개 행이 선택되었습니다. (1) : (job, hire), deptno별 연봉 합계
(2) : (job, hire)별 연봉 합계
(3) : 전체 연봉 합계
묶어준 (job, hire)를 하나로 보고 ROLLUP 해준다.
2) CUBE
그룹핑하는 컬럼의 모든 경우의 수에 대해 소계와 총계 조회
다시 부서별, 입사년도별, 직무별 연봉합계를 구하는데, 이번에는 CUBE 함수를 사용해보자.
SELECT deptno, to_char(hiredate, 'yyyy') AS hire, job, SUM(sal)
FROM emp
GROUP BY CUBE(deptno, to_char(hiredate, 'yyyy'), job)
ORDER BY 1,2,3 ;DEPTNO HIRE JOB SUM(SAL)
------ ---- ----------- --------
10 1981 MANAGER 2450 --- (1)
10 1981 PRESIDENT 5000
10 1981 7450 --- (2)
10 1982 CLERK 1300
10 1982 1300
10 CLERK 1300 --- (3)
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 --- (4)
20 1980 CLERK 800
20 1980 800
20 1981 ANALYST 3000
20 1981 MANAGER 2975
20 1981 5975
20 1982 ANALYST 3000
20 1982 3000
20 1983 CLERK 1100
20 1983 1100
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 1981 CLERK 950
30 1981 MANAGER 2850
30 1981 SALESMAN 5600
30 1981 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
1980 CLERK 800 --- (5)
1980 800 --- (6)
1981 ANALYST 3000
1981 CLERK 950
1981 MANAGER 8275
1981 PRESIDENT 5000
1981 SALESMAN 5600
1981 22825
1982 ANALYST 3000
1982 CLERK 1300
1982 4300
1983 CLERK 1100
1983 1100
ANALYST 6000 --- (7)
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
29025 --- (8)
49개 행이 선택되었습니다. (1) : deptno, hire, job별 연봉합계
(2) : deptno, hire별 연봉합계
(3) : deptno, job별 연봉합계
(4) : deptno별 연봉합계
(5) : hire, job별 연봉합계
(6) : hire별 연봉합계
(7) : job별 연봉합계
(8) : 전체 연봉합계
CUBE 함수 내에 n개 컬럼을 넣었을 때, 총 결과 그룹은 2^n개가 출력된다.
