쿼리에 내장된 또다른 쿼리 블록을 서브쿼리라고 하는데, 그 중에서 함수처럼 한 레코드당 정확히 하나의 값만을 리턴하는 서브쿼리를 '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