[Oracle] SET 연산자

# SET 연산자 

•  작성 방법 : SELECT 문 + SET 연산자 + SELECT 문 + SET 연산자 + ...  
•  집합 연산자의 경우 우선순위가 없어 순차적으로 처리함 (괄호 사용 시 우선순위 지정 가능)  
•  SELECT절의 컬럼 수와 데이터 타입이 일치해야 함 (컬럼명은 일치하지 않아도 됨)  
•  집합 연산의 경우 첫번째로 쓰여진 SQL 문이 기준  
•  ORDER BY절은 맨 마지막 SQL문 명령문 한 번밖에 쓸 수 없으며, 헤드 기준 컬럼명을 써야 함(컬럼명으로 정렬하기 보다는 컬럼 순서(1,2,3...)로 정렬하기를 권장)

  

합집합 

•  메모리 상에 정렬 후 출력  

-- UNION : 중복된 행을 제외하고 결과 출력, 결과값 정렬 수행  
SELECT deptno, ename, job 
FROM   emp 
WHERE  job = 'MANAGER' 
UNION 
SELECT deptno, ename, job 
FROM   emp 
WHERE  deptno = 20; 

-- UNION ALL : 중복을 허용하는 합집합, 정렬 미수행. 각 순서대로 결과값 출력
SELECT deptno, ename, job 
FROM   emp 
WHERE  job = 'MANAGER' 
UNION ALL 
SELECT deptno, ename, job 
FROM   emp 
WHERE  deptno = 20;

-- 컬럼개수, 데이터타입만 맞추어 줄 경우 합집합 연산 가능
SELECT empno, ename, hiredate
FROM   emp
UNION ALL
SELECT deptno, dname, TO_DATE(SYSDATE) -- 혹은 NULL도 가능
FROM dept;

     EMPNO ENAME                        HIREDATE -- 첫번째 SQL문의 HEAD로 출력
---------- ---------------------------- --------
      7369 SMITH                        80/12/17
      7499 ALLEN                        81/02/20
      7521 WARD                         81/02/22
      7566 JONES                        81/04/02
      7654 MARTIN                       81/09/28
      7698 BLAKE                        81/05/01
      7782 CLARK                        81/06/09
      7788 SCOTT                        82/12/09
      7839 KING                         81/11/17
      7844 TURNER                       81/09/08
      7876 ADAMS                        83/01/12
      7900 JAMES                        81/12/03
      7902 FORD                         81/12/03
      7934 MILLER                       82/01/23
        10 ACCOUNTING                   NULL
        20 RESEARCH                     NULL
        30 SALES                        NULL
        40 OPERATIONS                   NULL

  

교집합 

SELECT deptno, ename, job 
FROM   emp 
WHERE  job = 'MANAGER' 
INTERSECT 
SELECT deptno, ename, job 
FROM   emp 
WHERE  deptno = 20;

  

차집합 

SELECT deptno, ename, job 
FROM   emp 
WHERE  job = 'MANAGER' 
MINUS 
SELECT deptno, ename, job 
FROM   emp 
WHERE  deptno = 20;

 

연습문제 

부서별, 직무별 근무인원을 출력하는 문장을 작성하되, 소계와 총계도 출력

-- [나의 답안]
SELECT deptno, job, COUNT(*)
FROM   emp
GROUP BY deptno, job
UNION
SELECT deptno, NULL, COUNT(*)
FROM   emp
GROUP BY deptno
ORDER BY deptno, job;

-- [정답1]
-- ① 각 부서별, 직무별 근무 인원 출력하는 SQL
SELECT deptno, job, COUNT(*)
FROM   emp
WHERE deptno = 10
GROUP BY deptno, job;

-- ② 각 부서별 근무인원 출력하는 SQL
SELECT deptno, COUNT(*)
FROM   emp
WHERE deptno = 10
GROUP BY deptno;

-- ① + ②를 모든 부서에 적용
SELECT deptno, job, COUNT(*)
FROM   emp
WHERE deptno = 10
GROUP BY deptno, job
UNION ALL
SELECT deptno, NULL, COUNT(*)
FROM   emp
WHERE deptno = 10
GROUP BY deptno

UNION ALL

SELECT deptno, job, COUNT(*)
FROM   emp
WHERE deptno = 20
GROUP BY deptno, job
UNION ALL
SELECT deptno, NULL, COUNT(*)
FROM   emp
WHERE deptno = 20
GROUP BY deptno

UNION ALL

SELECT deptno, job, COUNT(*)
FROM   emp
WHERE deptno = 30
GROUP BY deptno, job
UNION ALL
SELECT deptno, NULL, COUNT(*)
FROM   emp
WHERE deptno = 30
GROUP BY deptno

UNION ALL

SELECT NULL, NULL, COUNT(*)
FROM   emp
WHERE  deptno in (10,20,30);

-- [정답2]
-- ① 각 부서/직무별 근무 인원
SELECT deptno, job, COUNT(*)
FROM   emp
WHERE  deptno IN (10, 20, 30)
GROUP BY deptno, job;

-- ② 각 부서별 근무 인원
SELECT deptno, COUNT(*)
FROM   emp
WHERE  deptno IN (10, 20, 30)
GROUP BY deptno;

-- ③ 모든 근무 인원
SELECT COUNT(*)
FROM   emp
WHERE  deptno IN (10, 20, 30);

-- ①+②+③
SELECT deptno, job, COUNT(*)
FROM   emp
WHERE  deptno IN (10, 20, 30)
GROUP BY deptno, job

UNION
-- ALL

SELECT deptno, NULL, COUNT(*)
FROM   emp
WHERE  deptno IN (10, 20, 30)
GROUP BY deptno

UNION
-- ALL

SELECT NULL, NULL, COUNT(*)
FROM   emp
WHERE  deptno IN (10, 20, 30);

-- UNION ALL을 사용해서 동일한 결과를 얻기 위해서는 정렬 수행 필요
ORDER BY deptno, 2; -- 컬럼명으로 정렬하기 보다는 컬럼 순서(1,2,3...)로 정렬하기를 권장