scalar subquery 두 개 이상의 값을 리턴하고 싶을 때

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

아래 쿼리는 위치가 'CHICAGO' 인 부서만 대상으로 급여 수준을 집계
사원(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)
where d.loc= 'CHICAGO'        

그렇다고 하단같이 쿼리 작성하면 emp에서 같은 범위를 반복적으로 엑세스 하는 비효율이 생긴다.

select d.deptno, d.dname,
(select avg(sal) from emp where deptno=d.deptno),
(select min(sal) from emp where deptno=d.deptno),
(select max(sal) from emp where deptno=d.deptno)                    
from dept d 
where d.loc= 'CHICAGO'

 

이럴 땐, 구하고자 하는 값들을 모두 결합 후 바깥쪽 액세스 쿼리에서 substr 함수로 분리한다.
실행 순서가 from 이 select보다 앞서므로 가능 하다...!!!!!

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