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

1. union all

-- 집합

-- 1. union all (통으로 붙임, column 수가 맞아야한다.)
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
-- null값을 넣지 않으면 column 수가 맞지 않아서 합쳐지지 않음
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

-- 2. union (중복을 제거하고 통으로 붙인다.)
-- (합집합 - 중복 제거 (연산이 든다))
select *
from dept
where deptno > 10 -- 20, 30, 40
union
select *
from dept
where deptno < 30; -- 10, 20

3. 교집합

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

4. 차집합

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