두 개 이상의 값을 리턴하고 싶을 때 - SQL전문가 가이드 p.653~p.654

 

목적: 'CHICAGO'인 부서(dept)만 대상으로 급여 수준을 집계

 

1차 단계
 (가) 사원(emp) 테이블 전체를 다 읽어야 하는 비효율
  - 아래의 두 쿼리는 동일한 쿼리다.

 SELECT D.DEPTNO, D.DNAME, AVG_SAL, MIN_SAL, MAX_SAL
 FROM DEPT D RIGHT OUTER JOIN
             (SELECT DEPTNO, AVG(SAL) AVG_SAL, MIN(SAL) MIN_SAL, MAX(SAL) MAX_SAL
             FROM EMP GROUP BY DEPTNO) E
 ON E.DEPTNO = D.DEPTNO
 WHERE D.LOC = 'CHICAGO'
 ;

 

 SELECT D.DEPTNO, D.DNAME, AVG_SAL, MIN_SAL, MAX_SAL
 FROM DEPT D 
     ,(SELECT DEPTNO, AVG(SAL) AVG_SAL, MIN(SAL) MIN_SAL, MAX(SAL) MAX_SAL
             FROM EMP GROUP BY DEPTNO) E
 WHERE E.DEPTNO = D.DEPTNO(+)
 AND D.LOC = 'CHICAGO'
 ;

 (나) 스칼라 서브쿼리는 한 레코드당 하나의 값만 리턴한다.

  - 아래와 같이 작성이 불가하다.

  SELECT D.DEPTNO, D.DNAME

              , (SELECT AVG(SAL), MIN(SAL), MAX(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO)

  FROM DEPT D

  WHERE D.LOC = 'CHICAGO'

  ;

 

 (다) EMP에서 같은 범위를 반복적으로 액세스하는 비효율이 생긴다.

  SELECT D.DEPTNO, D.DNAME

              , (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) AVG_SAL

              , (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) MIN_SAL

, (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) MAX_SAL

  FROM DEPT D

  WHERE D.LCO = 'CHICAGO'

  ;

 

2차 단계 - 구하고자 하는 값들을 모두 결합하고서 바깥쪽 액세스 쿼리에서 SUBSTR 함수로 분리

SELECT DEPTNO, DNAME

, TO_NUMBER(SUBSTR(SAL, 1, 7)) AVG_SAL

, TO_NUMBER(SUBSTR(SAL, 8, 7)) MIN_SAL

, TO_NUMBER(SUBSTR(SAL, 15)) MAX_SAL

FROM (

  SELECT D.DEPTNO, D.DNAME

               , (SELECT LPAD(AVG(SAL), 7) || LPAD(MIN(SAL), 7) || MAX(SAL)

FROM EMP WHERE DEPTNO = D.DEPTNO) SAL

  FROM DEPT D

  WHERE D.LOC = 'CHICAGO'

  ;