[Oracle] 서브쿼리 - INLINE 뷰, WITH 구문

# INLINE 뷰 

- FROM절에 사용한 서브쿼리 

- SQL문이 실행될 때만 임시적으로 생성되는 동적 뷰 

SELECT * 
FROM (SELECT job, ROUND(AVG(sal)) 
      FROM   emp 
      GROUP BY job 
      ORDER BY ROUND(AVG(sal)) DESC) 
WHERE ROWNUM <= 3;

  

# VEIW 

- DDL로 생성되는 정적 뷰  

-- ① VIEW 생성 
CREATE OR REPLACE VIEW job_v 
AS 
SELECT job, ROUND(AVG(sal)) avg_sal -- ALIAS 작성 필요 
       FROM   emp 
       GROUP BY job 
       ORDER BY ROUND(AVG(sal)) DESC; 

-- ② VIEW 조회 시 인라인뷰 조회한 것과 동일한 결과 
SELECT *  
FROM   job_v 
WHERE  ROWNUM <=3;

  

# WITH절 

- VIEW와 동일한 역할 수행하나, VIEW는 먼저 만들어두고 실행하는 반면 WITH는 SELECT 절 앞에 작성 후 사용 

- FROM절이나 VIEW는 실행된 후 메모리에 만들어지고 메인 쿼리에서 사용 

- WITH절은 복합 질의에서 여러번 발생하는 같은 질의 블록 결과를 사용자 임시테이블스페이스에 저장함 

WITH abc AS (SELECT job, ROUND(AVG(sal)) avg_sal -- ALIAS 작성 필요 
             FROM   emp 
             GROUP BY job 
             ORDER BY ROUND(AVG(sal)) DESC) 

SELECT * 
FROM   abc 
WHERE  ROWNUM <= 3;

-- FROM절 사용: 서브쿼리, 메인쿼리마다 테이블 값을 읽어 그룹핑한 결과치 생성 
SELECT deptno, SUM(sal) 
FROM   emp 
GROUP BY deptno 
HAVING SUM(sal) > (SELECT AVG(SUM(sal)) -- 비상관서브쿼리 
                   FROM  emp 
                   GROUP BY deptno); 
               
-- WITH절 사용 : 해당 절이 쿼리에서 여러번 사용되더라도 한 번만 실행하므로 성능 향상 
WITH with_tab AS (SELECT deptno, SUM(sal) sum -- 한 번 사용 
                  FROM emp 
                  GROUP BY deptno) 
SELECT * 
FROM   with_tab 
-- 두 번 사용 
WHERE  sum > (SELECT AVG(sum) FROM with_tab);

  

# 연습문제 

가장 많은 사원이 근무하고있는 부서의 부서번호, 부서명, 근무인원수, 평균급여를 출력 

SELECT t.deptno, t.dname, t.cnt, t.avg_sal 
FROM  (SELECT d.deptno, d.dname, count(*) cnt, AVG(e.sal) avg_sal 
       FROM   emp e JOIN dept d 
       ON     e.deptno = d.deptno 
       GROUP BY d.deptno, d.dname 
       ORDER BY avg_sal DESC) t 
WHERE ROWNUM = 1;