sql중급 / 집합연산자

067 집합 연산자로 데이터를 위아래로 연결하기 1(UNION ALL)

데이터를 양옆으로 연결해서 출력하는 것 = join

데이터를 위아래로 연결해서 출력하는 것 = 집합연산자

ㅇ집합연산자의 종류

 1. union all : 합집합

 2. union : 합집합

 3. intersect : 교집합

 4. minus : 차집합

문제319. union all을 이용해서 데이터를 위아래로 연결해서 출력하는데 삶의 주관적 만족도가 남자의 데이터와 여자의 데이터를 다음과 같이 위아래로 연결해서 출력하시오

select feature2, extr_satifi + few_satifi as 만족도, rank() over (order by extr_satifi + few_satifi desc) as 순위
         from satisfy_table
         where feature2 like '%*남자'
union all
select feature2, extr_satifi + few_satifi as 만족도, rank() over (order by extr_satifi + few_satifi desc) as 순위
         from satisfy_table
         where feature2 like '%*여자' ;

ㅇ두 쿼리의 데이터를 위아래로 연결해서 출력하려면 지켜줘야할 조건

1. 위쪽 쿼리문의 컬럼의 갯수와 아래쪽 쿼리문의 컬럼의 갯수가 동일해야함

2. 위쪽 쿼리문의 컬럼의 데이터 타입과 아래쪽 쿼리문의 컬럼의 데이터 타입이 동일해야함

3. order by 절은 맨 밑의 쿼리문 아래쪽에만 작성할 수 있음

문제320. 혼인상태에 대한 만족도 순위와 경제활동상태에 대한 만족도와 순위 쿼리문의 결과를 위아래로 연결해서 출력하시오

select feature2, extr_satifi + few_satifi as 만족도, rank() over (order by extr_satifi + few_satifi desc) as 순위
         from satisfy_table
         where feature1 = '혼인상태'
union all
select feature2, extr_satifi + few_satifi as 만족도, rank() over (order by extr_satifi + few_satifi desc) as 순위
         from satisfy_table
         where feature1 = '경제활동상태';

--예제67. 부서번호와 부서번호별 토탈 월급을 출력하는데, 전체토탈 월급도 출력하세요
select deptno, sum(sal)
 from emp
 group by deptno
union all
select null, sum(sal)
 from emp;

문제322. 아래의 sql 결과를 rollup을 이용하지 말고 union all을 이용해서 출력하시오

select job, sum(sal)

 from emp

 group by rollup(job);

select job, sum(sal)
 from emp
 group by job
union all
select null, sum(sal)
 from emp
 order by 1 asc;
 
 -- 사실 rollup하면 간단히 해결가능

323. 아래의 튜닝후 SQL을 튜닝전 SQL로 변경하는데 아래의 cube로 작성한 sql을 union all로 변경하세요

select to_char(hiredate,'RRRR'), sum(sal)
 from emp
 group by cube(to_char(hiredate,'RRRR' ));

select to_char(null), sum(sal)
 from emp
 union all
select to_char(hiredate,'RRRR'), sum(sal)
 from emp
 group by to_char(hiredate,'RRRR')
 order by 1 nulls first;

 

 

068 집합 연산자로 데이터를 위아래로 연결하기 2(UNION)

-- 부서번호와 부서번호별 토탈월급을 출력하는데, 맨 아래에 전체토탈월급을 출력하고 부서번호를 오름차순으로 정렬하시오
select deptno, sum(sal)
 from emp
 group by deptno
union
select to_number(null) as deptno, sum(sal)
 from emp;

/*
 union이 union all과 다른점은 다음 2가지
 1. 첫번재 컬럼을 기준으로 데이터 정렬(21c버전에서는 데이터 정렬기능 삭제 > 필요한 경우 사용자가 알아서 order by 사용하도록)
 2. 중복된 행 제거
*/

문제324. 아래의 SQL의 결과를 rollup사용하지 말고 union으로 수행하시오

select to_char(hiredate, 'RRRR'), avg(sal), sum(sal), max(sal), min(sal)

 from emp

 group by rollup(to_char(hiredate,'RRRR') );

select to_char(hiredate, 'RRRR'), avg(sal) 평균, sum(sal) 토탈, max(sal) 최대, min(sal) 최소
 from emp
 group by to_char(hiredate,'RRRR')
union
 select null, avg(sal) 평균, sum(sal) 토탈, max(sal) 최대, min(sal) 최소
 from emp;

ㅇ union과 union all의 위아래의 쿼리문 테이블이 서로 달라도 위아래로 연결 가능

 

 

069 집합 연산자로 데이터의 교집합을 출력하기(INTERSECT)

--예제69. 교집합이 출력되어서 부서번호 20번인 사원들의 데이터만 출력됨
select ename, sal, job, deptno
 from emp
 where deptno in (10,20)
intersect
 select ename, sal, job, deptno
 from emp
 where deptno in(20,30);

문제325. 부서테이블에서 부서번호를 출력하는데 사원테이블의 있는 부서번호만 출력하시오

select deptno
 from dept
intersect
select deptno
 from emp;

 

070 집합 연산자로 데이터의 차이를 출력하기(MINUS)

-- 예제70. 차집합 결과가 출력되므로 부서번호 10번만 출력됨.
select ename, sal, job, deptno
 from emp
 where deptno in (10,20)
minus
select ename, sal, job, deptno
 from emp
 where deptno in (20,30)
 -- 위의 쿼리문을 기준으로 아래 쿼리문의 데이터를 모두 빼버리는 것