[Database] 15. 집합

-- 집합

-- 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;

-- group by로 묶기
select sum(sal), deptno
from emp
group by deptno
union all
select sum(sal), null
from emp;

-- 2. union (합집합 - 중복을 제거한다(연산을 한 번 더 해서 느리다.))
-- 20이 중복되었지만 제거하고 출력되었다.
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
INTERSECT
select *
from dept
where deptno < 30;

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