[DB] 9. 집합

1. union all (전체를 합침)

• 사용 시 컬럼의 개수를 동일하게 만들어야 함

select sum(sal), deptno
from emp
where deptno = 10;

select sum(sal), deptno
from emp
where deptno = 20;

select sum(sal), deptno
from emp
where deptno = 30;

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;

• 총합을 붙이고 싶다

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;

• group by를 사용하면 간결하다.

2. union (중복을 제거하고 합침)

select *
from dept
where deptno > 10;

select *
from dept
where deptno < 30;

select *
from dept
where deptno > 10
union
select *
from dept
where deptno < 30;

• 중복된 20번 부서가 하나로 줄어듦

select *
from dept
where deptno > 10
union all
select *
from dept
where deptno < 30;

• all을 사용하면 전부 합쳐짐

3. intersect (교집합)

select *
from dept
where deptno > 10;

select *
from dept
where deptno < 30;

select *
from dept
where deptno > 10
intersect
select *
from dept
where deptno < 30;

4. except (차집합)

select *
from dept
where deptno > 10;

select *
from dept
where deptno < 30;

select *
from dept
where deptno > 10
except
select *
from dept
where deptno < 30;

• except 기준 왼쪽이 기준 테이블