[DB] 9. 집합

1. 합집합

1. union all

-- 집합

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

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

select *
from dept
where deptno > 10 -- 20, 30, 40
union all
select *
from dept
where deptno < 30; -- 10, 20

2. 교집합

-- 3. intersect (교집합)
select *
from dept
where deptno > 10 -- 20, 30, 40
INTERSECT
select *
from dept
where deptno < 30; -- 10, 20

3. 차집합

-- 4. except (차집합)
select *
from dept
where deptno > 10 -- 20, 30, 40
EXCEPT
select *
from dept
where deptno < 30; -- 10, 20

전체 코드

-- 집합

-- 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 (차집합)
select *
from dept
where deptno > 10 -- 20, 30, 40
EXCEPT
select *
from dept
where deptno < 30; -- 10, 20