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

1. 합집합

-- 1. 집합

-- 1. union all (합집합 - 중복제거 X, 그냥 다 붙이기)
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와 union all 쓴 거
select sum(sal), deptno
from emp
group by deptno
union all
select sum(sal), null
from emp;

2. 합집합 (중복 제거)

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

3. 교집합

-- 3. intersept (교집합 - 툴이 빨간색 밑줄 표시 되지만 실행은 됨)
select *
from dept
where deptno > 10 -- 20, 30, 40
intersect
select *
from dept
where deptno < 30; -- 10, 20

4. 차집합

-- 4. except (차집합 - 파란글씨로 뜨나 빨간 밑줄은 뜸 - 실행 됨)
select *
from dept
where deptno > 10 -- 20, 30, 40
except
select *
from dept
where deptno < 30; -- 10, 20