[SQLD]빈출 서브쿼리, 집합연산자, 합계 연산자 총정리(예시포함)

1. 다중 행 서브쿼리

 

1. ANY (=SOME)

• 서브 쿼리에서 여러 개의 값이 나오게 되는 경우 이 값들에 대해 어떤 한 값보다만 어떻다라고 비교할 때 사용
• 즉, 메인쿼리의 비교 조건이 서브쿼리의 검색 결과와 하나 이상만 일치하면 참이다.
• '> ANY'는 찾아진 값에 대해서 하나라도 크면 참이다. 그러므로 찾아진 값 중에서 가장 작은 값 즉, 최소값보다 크면 참이 된다. (-> ALL의 경우 최대값보다 커야한다.)
• SAL > ANY (500, 600, 700)은 SAL> 500 OR SAL> 600 OR SAL> 700과 동일하고 결국 SAL>500만 충족하면 된다.

 

* 양 쪽의  SQL문은 동일한 결과를 출력한다.

SELECT ename, sal FROMM emp_tbl WHERE sal > ANY (SELECT sal From emp_tbl                                  WHERE deptno = 30);

SELECT ename, sal FROMM emp_tbl WHERE sal > (SELECT MIN(sal) From emp_tbl                          WHERE deptno = 30);

 

 

• 영업 사원들의 최소 급여 보다 급여를 많이 받는 사원들의 이름과 급여와 직급을 출력하되 영업 사원은 출력X

 

 

2. EXIST / NOT EXIST

 

• 서브쿼리의 데이터가 존재하는 지 여부를 먼저 따져 존재하는 값들만 결과로 출력한다. 
• 적어도 하나의 행을 돌려주면  TRUE가 된다.
• NOT EXIST는 서브 쿼리가 아무것도 돌려주지 않으면  TRUE가 된다.
• 관리자로 등록되어 있는 사원을 조회하기

SELF JOIN

• 테이블을 자기 자신과 조인하는 기법. 테이블에 있는 두 개의 다른 행이 서로 어떤 관계를 가지고 있는지 찾을 때 유용하다.
• 부서테이블에서 사원을 한명이라도 가지고 있는 부서명, 부서번호를 출력하시오

                                     

• NOT EXISTS

 

 

 

 

2. 집합 연산자(SET)

• 두 개 이상의 쿼리 결과를 하나로 결합하는 연산자이다.
• 여러 개의  SELECT문을 하나로 연결한다.
• 일반적으로 조인, 서브 쿼리의 기능을 단순하게 구현한다.
• 집합 연산자로 결되는 결과의 컬럼은 데이터타입이 동일해야한다.

 

1. UNION: 합집합

• 여러개의 SQL문의 결과에 대한 합집합을 반환한다.
• 중복행을 하나의 행으로 보여준다
• 영업사원과 관리자를 따로 질의해서  UNION으로 결합하기

 

 

 

2. UNION ALL: 합집합

• UNION과 동일한 기능이지만 중복행도 모두 추력한다.
• 급여가 1000 미만인 직원, 2000 미만인 직원을 조회하여 UNION ALL 로 결합해 보자. 중복 결과를 포함하여 출력한다.

 

 

 

3. INTERSECT : 교집합

• 여러 개의  SQL문의 결과에 대한 교집합을 반환한다.
• 중복 행은 하나의 결과로 보여준다
• EXISTS 또는 IN 서브쿼리를 이용한  SQL문으로 변경 가능하다.
• 급여가 1000 초과인 직원을 조회하고 2000 미만인 직원을 각각 조회하여 INTERSECT로 결합해 보자. 급여가 1000 초과 2000 미만인 직원들만 조회한다

 

 

 

 

4. MINUS : 차집합

• 여러 개의 SQL문의 결과에 대한 차집합을 반환한다.
• 중복 행은 하나의 결과로 보여준다
• NOT EXISTS 또는 NOT IN 서브쿼리를 이용한 SQL문으로 변경 가능하다
• 사원 전체 결과에서 MINUS 연산자를 이용해 급여가 2000 초과인 사원 제외하여 조회해 보자.

 

 

 

 

3. 집계함수, 그룹함수: ROLLUP, CUBE

• GROUP BY절에서 사용되는 집계함수로 그룹에 대한 소계(SUBTOTAL)과 총계(GRAND TOTAL)을 제공한다.
• GROUPING함수와 CASE함수를 이용하면 결산 관련 보고서의 포맷을 작성할 수 있다.
• 일반적인 GROUP BY절 사용시 생성되는 급여합은 9개이다.

 

 

1. ROLLUP

• 인수에 대한 소계와 총계를 나타낸다.
• 그룹 소계 -> 총계 순으로 레벨이 낮은 것부터 오름차순으로 출력된다.(L1->L2->L3)
• ROLLUP의 경우 계층 간 집게에 대해서는 LEVEL 별 순서(L1->L2->L3)를 정렬하지만, 계층 내 GROUP BY 수행시 생성되는 표준 집계에는 별도의 정렬을 지원하지 않는다.

L1 - GROUP BY 수행시 생성되는 표준 집계 (9건) L2 - DNAME 별 모든 JOB의 SUBTOTAL (3건) L3 - GRAND TOTAL (마지막 행, 1건)

• 그룹핑 되는 칼럼의 수를  N개라고 했을 때, N+1 레벨의 소계가 생성된다.
• 인수의 순서에의해 영향을 받기 때문에 주의해야 한다. -> 첫번째 행 기준으로 소계가 출력된다
• 예시에서 부서는 3개로 그룹핑 -> 합계는 4개 도출(3+1)
• ROLLUP(A, B, C) -> (A,B,C)/ (A,B) / (A)/ (총합계)

 

 

** 인수의 순서를 바꿀경우: JOB 컬럼의 종류는 5개 이므로 총 6개의 소계 및 합계가 출력된다.

JOB을 인자로 하기 때문에 JOB칼럼의 원소 종류  5개 +1로 합계가 6개 출력된다.

 

 

2. GROUPING 함수

• ROLLUP이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(컬럼명) = 1 이 표시되고, 그 외의 결과에는 GROUPING(컬럼명) = 0 반환
• CASE/DECODE 를 이용해, 소계를 나타내는 필드에 원하는 문자열을 지정할 수 있다.
• ROLLUP 함수를 추가한 집계 보고서에서 집계 레코드를 구분할 수 있는 GROUPING 함수가 추가된 SQL
• ROLLUP에 의해 소계된 컬럼은 GROUPING함수에 의해 1로 표시되고 아닌 경우에는 0이다
• 1과 0의 값에 따라 DECODE 조건을 걸어 소계가 걸리면 빈 공란으로 두지 않고 명칭을 부여한다

 

CASE문을 사용한 경우,IF ELSE문법과 동일 첫번째 CASE가 만족이 안되면 두번째 CASE로

 

 

join하는 경우

 

 

 

 

 

 

**DNAME은 표준방식으로하고  JOB만 ROLLUP한 경우

- DNAME은 소계가 생기지 않아 DECODE에 걸리지 않고 따라서 부서별 급여합은 출력되지 않는다.

 

 

 

 

 

 

3. CUBE

• 그룹핑 컬럼이 가질 수 있는 모든 경우의 수에 대하여 소계와 총계 생성
• GROUP BY 항목들간 다차원적인 소계를 계산할 수 있다(모든 그룹케이스를 계산함) -> 성능저하
• ROLLUP과는 반대로 총합->소계순으로 출력한다.
• ROLLUP과는 다르게 인자별로 소계가 생성된다. 그룹핑 컬럼이 N개라고 할때 2의 N승의 개수만큼 소계를 생성한다.
• CUBE함수 없이 모든 경우의  소계를 집계할 경우  UNION ALL을 4번 써야하기 때문에 복잡해진다.
• CUBE(A, B, C) -> (A, B, C) / (A, B) / (A, C) / (B, C) / (A) / (B) / (C) / ()

 

 

 

3. GROUPING SETS

• ROLLUP이나 CUBE와는 다르게 계층이 나타나지 않고 그룹핑된 결과값만을 보여준다
• GROUPING SETS은 괄호로 묶은 집합별로의 집계 역시 구할 수 있다.
• GROUPING SETS은 특정항목을 그룹한 다수의  SQL들을 UNION한 것과 같다.
• GROUPING SETS(인수1, 인수2) 와 GROUPING SETS(인수2, 인수1)의 결과는 서로 같다.
• 즉, GROUPING SETS 인수들은 평등한 관계이기 때문에 순서와 무관하다.
• GROUPING SETS 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명식적으로 정렬 컬럼이 표시가 되어야 한다.

 

 

 

[예제1] 부서-직업-매니저 별, 부서-직업 별, 직업-매니저 별 집계를 GROUPING SETS 함수를 이용해서 구해라.