06. 스칼라 서브쿼리를 이용한 조인

h5.(1)스칼라 서브쿼리

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

elect /*+ ordered use_nl(d) */ empno, ename, sal, hiredate, dname
from emp e, dept d
where d.deptno(+)=e.deptno and sal >= 2000;

h5.(2)스칼라 서브쿼리의 캐싱 효과

스칼라 서브쿼리 수행횟수를 최소화하려고 그 입력 값과 출력 값을 내부 캐시 (Query Execution Cache)에 저장해 둔다.
스칼라 서브쿼리가 수행될 때면 일단 '입력 값' 을 캐시에서 찾아 보고 거기 있으면 저장된 '출력 값'을리턴한다. 캐시에서 찾지 못할때만쿼리를수행하며,
결과는 버리지 않고 캐시에 저장해 둔다.

select empno, ename, sal, hiredate
      ,(select d.dname            -> 출력갑
         from dept d 
        where d.deptno=e.deptno   -> 입력값
      )
from emp e
where sal >= 2000

select empno, ename, sal, hiredate
      ,(select get_name(deptno) from dual) dname
from emp e
where sal >= 2000

h5.(3)두 개 이상의 값을 리턴하고 싶을 때

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'

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'       

select d.deptno, d.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.name
             ,(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'
     )

create or replace type sal_type as object
( avg_sal number, min_sal number, max_sal number)

 
select deptno, dname
     , a.avl.svg_sal, a.sal.min_sal, a.sal.max_sal
from (
       select d.deptno, d.name
             ,(select sal_type( avg(sal), min(sal), max(sal) )
                from emp where deptno=d.deptno) sal
       from dept d
       where d.loc = 'CHICAGO'
     ) a