SQL - 집합 연산

1. 집합 연산

(UNION, UNION ALL, INTERSECT, MINUS)

집합연산 union / union all/ intersect / minus
--------------------------------------------------------------------------
-- 테이블 복제
CREATE TABLE EMP_history as (select * from emp where deptno = '10');
SELECT * FROM EMP_HISTORY;
 
INSERT INTO EMP_history VALUES
(9999,'GUGUGU','SALESMAN',7698,TO_DATE('28-9-1981','dd-mm-yyyy'),1300,0,10);
COMMIT;
 
-- union / union all
SELECT * FROM EMP_HISTORY
UNION
SELECT * FROM EMP
;
 
(SELECT * FROM EMP_HISTORY)
UNION ALL
(SELECT * FROM EMP)
;
 
select (sysdate-hiredate)/365 from emp;
 
-- 정렬을 해야 하는 경우 - inline뷰
SELECT *
FROM (
      (SELECT * FROM EMP_HISTORY)
      UNION ALL
      (SELECT * FROM EMP)
    )
order by ename;
 
 
 
-- INTERSECT (교집합)
SELECT * from EMP
INTERSECT
SELECT * FROM EMP_HISTORY
;
 
-- MINUS (차집합)
SELECT * from EMP
MINUS
SELECT * FROM EMP_HISTORY
;
 
SELECT * from EMP_HISTORY
MINUS
SELECT * FROM EMP
;
 
-- 가독성을 위해 괄호 습관화
(SELECT EMPNO as eno, ENAME, 'ssss' col1 FROM EMP)
UNION 
(select 111, 'abc', 'abc' from dual)
UNION 
(select 222, 'fgh','fgh' from dual);
 
-- 타입을 맞춰야 함 :  to_char(sal)
(SELECT EMPNO , ENAME, to_char(sal) FROM EMP)
UNION 
(select 111, 'abc', 'abc' from dual)
UNION 
(select 222, 'fgh','fgh' from dual);