🌠3-4-4. 스칼라 서브쿼리

과목3. SQL 고급 활용 및 튜닝

제4장 조인 튜닝

제4절 스칼라 서브쿼리

쿼리에 내장된 또다른 쿼리 블록을 서브쿼리라고 하는데, 그 중에서 함수처럼 한 레코드당 정확히 하나의 값만을 리턴하는 서브쿼리를 'Scalar Subquery'라고 한다. Scalar Subquery는 주로 SELECT-list에서 사용되지만 몇 가지 예외사항을 뺀다면 칼럼이 올 수 있는 대부분 위치에서 사용 가능하다.

SELECT EMPNO
    , ENAME, SAL
    , HIREDATE
    , (SELECT D.DNAME
           FROM DEPT D
          WHERE D.DEPTNO = E.DEPTNO) dname
 FROM EMP E
WHERE SAL >= 2000 

Scalar Subquery를 사용한 위 쿼리 문장은 아래 Outer 조인문과 100% 같은 결과를 낸다. 즉 dept와 조인에 실패하는 emp 레코드가 있다면 dname으로 null 값이 출력된다.

SELECT /*+ ordered use_nl(d) */
      E.EMPNO, E.ENAME, E.SAL, E.HIREDATE, D.DNAME
 FROM EMP E
      RIGHT OUTER JOIN DEPT D
                    ON D.DEPTNO = E.DEPTNO
WHERE E.SAL >= 2000 

위에서 예시한 쿼리는 결과만 같은 것이 아니라 조인을 수행하는 처리 경로도 동일한데, NL 방식으로 수행되도록 힌트를 사용했기 때문이다. 다만 Scalar Subquery에는 내부적으로 캐싱 기법이 작용된다는 점이 다르고, 이를 이용한 튜닝이 자주 행해진다.

1. Scalar Subquery의 캐싱 효과 //TODO

2. 두 개 이상의 값을 리턴하고 싶을때

아래 쿼리는 위치가 'CHICAGO'인 부서(dept)만 대상으로 급여 수준을 집계하려는 것인데, 사원(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
    , (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.LOC = 'CHICAGO' 

이럴 때, 아래 처럼 구하고자 하는 값들을 모두 결합하고서 바깥쪽 액세스 쿼리에서 substr 함수로 분리하는 방법이 유용하게 쓰인다.

[예제] Oracle

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

[예제] SQL Server

SELECT DEPTNO
    , DNAME
    , CAST(SUBSTRING(SAL, 1, 7) AS FLOAT) avg_sal
    , CAST(SUBSTRING(SAL, 8, 7) AS INT)   min_sal
    , CAST(SUBSTRING(SAL, 15, 7) AS INT)  max_sal
 FROM (SELECT D.DEPTNO
              , D.DNAME
              , (SELECT STR(AVG(SAL), 7, 2) + STR(MIN(SAL), 7)
                        + STR(MAX(SAL), 7)
                   FROM EMP
                  WHERE DEPTNO = D.DEPTNO) sal
         FROM DEPT D
        WHERE D.LOC = 'CHICAGO') X 

3. 스칼라 서브 쿼리 Unnesting //TODO

출처 : 데이터온에어 – 한국데이터산업진흥원(https://dataonair.or.kr)