[데이터베이스 집합] 18. 집합

-- 집합

-- 1. union all (통으로 붙인다. 칼럼 개수가 동일해야 한다)
select sum(sal), deptno
from emp
where deptno = 10
union all
select sum(sal), deptno
from emp
where deptno = 20
union all
select sum(sal), deptno
from emp
where deptno = 30
union all
select sum(sal), null
from emp;

select sum(sal), deptno
from emp
group by deptno
union all
select sum(sal), null
from emp;

-- 2. union (합집합 - 중복 제거(연산이 든다))
select *
from dept
where deptno > 10 -- 20, 30, 40
union
select *
from dept
where deptno < 30; -- 10, 20

-- 3. intersect (교집합 - 툴이 정신 못차림)
select *
from dept
where deptno > 10 -- 20, 30, 40
INTERSECT 
select *
from dept
where deptno < 30; -- 10, 20

-- 4. EXCEPT (차집합 - 툴이 80프로정도 정신 못차림)
select *
from dept
where deptno > 10 -- 20, 30, 40
EXCEPT 
select *
from dept
where deptno < 30; -- 10, 20