SQL-15)집합연산자 - UNION, UNION ALL, INTERSECT, MINUS : sqld, sqld 자격증

안녕하세요 이승혁입니다.

 

지난 포스팅에서 서브쿼리에 대해 실습을 진행해 보았습니다.

이번 시간은 두 쿼리의 결과를 붙여서 출력하는 집합 연산자에 대해 실습을 진행해 보도록 하겠습니다.

 

※ 집합 연산자

 

      JOIN :  서로 다른 테이블의 칼럼을 양 옆으로 출력하는 문법

      집합 연산자 : 서로 다른 테이블의 컬럼을 위아래로 출력하는 문법이다

 

1. 합집합 연산자 : union, union all
2. 교집합 연산자 : intersect
3. 차집합 연산자 : minus

※ 집합 연산자 사용시 주의사항

 

1. 집합 연산자 위아래 쿼리의 컬럼의 갯수가 동일해야 한다.

    - 위의 쿼리의 컬럼의 갯수가 2개면 아래도 2개여야 한다.

 

2. 집합 연산자 위아래 쿼리의 컬럼의 데이터 타입 일치해야 한다.

    - 위 쿼리 컬럼 데이터 문자형이면 아래 순서의 컬럼도 문자형이어야 한다.

 

3. 컬럼 별칭은 맨 위 커리에만 사용할 수 있고 order by 절은 아래 쿼리에만 사용 가능하다.

---

ㆍ 집합연산자 - 합집합 연산자 union all

 

실습 1. 직업별 토탈월급, 사원 전체 월급 각각 출력해보기

select job,sum(sal)
from emp
group by job;

select '전체월급',sum(sal)
from emp;

실습 2. union all 사용해보기, 실습 1의 결과의 두 번재 쿼리를 아래로 붙여 출력해보기

select job,sum(sal)
from emp
group by job
union all
select '전체월급 : ',sum(sal)
from emp;

주의사항 1. 컬럼 개수 맞추기 ( job, sum(sal) 2개 / '전체월급 :', sum(sal) 2개)

            2. 데이터 타입 맞추기 ( 문자형 , 숫자형 / 문자형 , 숫자형 )

 

실습 3. union all, 입사한 년도 별 인원수와, 맨 아래 총 인원수를 인원수가 많은 순서대로 출력해보기

select to_char(hiredate,'RRRR') 입사년도 ,count(*) 사원수
from emp
group by to_char(hiredate,'RRRR')
union all
select '총 사원 :',count(*)
from emp
order by 2 desc;

주의사항 3. order by 절은 맨 아래, 컬럼 별칭은 위에 ! 

 

※ reporting 함수 - rollup

 

실습 4. 실습 1번의 쿼리를 emp 테이블은 한 번만 접근해서 출력해보기

select nvl(job,'총월급:') 직업명,sum(sal)
from emp
group by rollup(job);

nvl(null처리 함수)과 rollup 함수를 사용해서 집합 연산자를 사용하지 않고 나타낼 수 있다.

 

※ reporting 함수 - cube

 

실습 5. 실습 3번의 쿼리를 emp 테이블 한 번만 접근해서 출력해보기

select nvl(to_char(hiredate,'RRRR'), '총 사원 :') 입사년도,count(*)
from emp
group by cube(to_char(hiredate,'RRRR'));

nvl & cube 함수를 통해 집합 연산자를 사용하지 않고 한 번에 나타낼 수 있습니다.

 

rollup - 가장 아래 모든 그룸 함수 데이터의 합을 나타낸다.

cube - 가장 위에 모든 그룹 함수 데이터의 함을 나타낸다.

---

ㆍ 집합연산자 - 합집합 연산자 union

 

     union all과 같은 합집합 연산자입니다.

     union all과 다르게 중복된 데이터 제거, 정렬된 상태로 결과 출력합니다.

 

※ union 집합 연산자의 단점

 

     데이터 크기가 큰 경우 좋지 않다.

     굳이 정렬 할 필요가 없을 때 정렬에 시간이 소요된다.

 

※ SQL 튜너들이 SQL 튜닝할 때 union 을 사용한 SQL이 굳이 정렬할 필요가 없다면 union all 연산자로 변경합니다

 

실습 6. union , union all 비교해보기 , 실습 2번 문제를 union으로 변경해서 비교해 봅니다.

select job,sum(sal)
from emp
group by job
union 
select '전체월급 : ',sum(sal)
from emp;

그룹핑한 데이터를 정렬 후 출력됩니다

 

---

ㆍ 집합 연산자 - 교집합 연산자 intersect

 

      두 집합(두 쿼리)의 교집합을 출력하는 함수 입니다.

 

실습 7. intersect 사용해보기

select ename,job
from emp
intersect
select ename,job
from emp
where job in ('ANALYST','SALESMAN','CLERK');

두개의 결과 테이블 중 겹치는 데이터들만 출력해줍니다.

---

ㆍ 집합 연산자 - 차집합 연산자 minus

 

     두 집합간의 차이를 출력하는 집한 연산자입니다.

 

select  num                              t1   minus   t2      =   result

  from t1                                   1                             1

  minus                                     2                             2

  select num                               3             3

  from t2;                                   4             4

                                                              5

                                                              6

---

 

※ 고급 쿼리문 함수들

 

- Reporting 함수

   1. rollup

   2. cube

   3. grouping sets

   4. grouping

 

ㆍ 고급 함수 - grouping sets

 

     집계할 칼럼을 직접 선택해 집계할 수 있게 해주는 레포팅 함수

 

실습 8. union all -> grouping sets 바꿔보기

 

튜닝전 )

select null as deptno, job, sum(sal)
from emp
group by job
union all
select deptno, null as job, sum(sal)
from emp
group by deptno;

 

튜닝후 )

select deptno, job, sum(sal)
from emp
group by grouping sets((deptno),(job));

 

결과는 같지만 튜닝 과정을 통해 코드가 훨씬 짧아졌습니다.

또한 직관적으로 알기 쉽고, 내부적 코드 실행 순서도 더 간단해졌습니다.

 

실습 9. grouping sets 사용해보기, 부서번호, 직업, 부서번호별 직업 월급 합계를 출력해보기

select deptno, job, sum(sal)
from emp
group by grouping sets((deptno,job))
order by deptno;

grouping sets 함수의 인수 안에 부서번호와 직업을 함께 넣어 그룹핑 기준을 잡습니다.

따라서 그룹은 10 부서의 CLERK, 10번 부서의 MANAGER, 10번 부서의 PRESIDENT, 10번 부서의 ANALYST ...

                   20 부서의 CLERK, 20번 부서의 MANAGER, 20번 부서의 PRESIDENT, 20번 부서의 ANALYST ... 과 같이

그룹핑하게 됩니다.

 

ㆍ 고급 함수 - grouping 

 

     진짜 null값과, reporting 함수에 의해 나타내어지는 null을 구분하기 위한 함수입니다.

     진짜 null값은 0 , 레포팅 함수에 의한 null은 0으로 나타납니다.

select comm, sum(sal),grouping(comm)
from emp
group by rollup(comm);

마지막 행은 rollup 함수에 의해 나타내어지는 null이고, 5번째 행의 null은 실제 null값을 가진 행들입니다.

 

이번 포스팅은 집합 연산자와 reporting 함수들을 실습해 보았습니다.

다음 시간에는 exist, with절, 계층형 질의문에 대해 실습을 진행해 보도록 하겠습니다.

 

읽어주셔서 감사합니다.