[SQL] WITH절 (with ~ as)

봄·2025년 7월 16일

SQL

목록 보기

124/126

with절 하나로 쿼리가 두배 빨라짐
복잡한 쿼리가 간단해지는 기술
중급 개발자들이 실무에서 자주 사용하는 SQL 꿀팁

💡 WITH 절의 주요 장점

가독형 향상 : 복잡한 서브쿼리를 분리하여 쉽게 이해할 수 있는 형태로 작성가능
성능 최적화 : 동일한 서브쿼리가 반복될때 딱 한번만 실행하고 계속 재사용됨

실습1. 이름, 월급, 월급의 순위를 출력하시오

select ename, sal, dense_rank() over(order by sal desc) 순위
 from emp;

실습2. 위의 SQL에 from절의 서브쿼리를 이용해서 이름과 월급과 월급의 순위를 출력하는데 순위가 1등, 3등, 5등만 출력하시오

select *
from (
        select ename, sal, dense_rank() over(order by sal desc) 순위
         from emp)
where 순위 in (1, 3, 5);

💡 from절의 서브쿼리의 결과가 메모리로 올라가서 구성이 됨

실습3. 이번에는 with절로 from절의 서브쿼리 결과를 테이블로 구성하시오

with emp_rank as ( 
                  select /*+ materialize */ ename, sal, dense_rank() over (order by sal desc) 순위
                   from emp
                  )
select *
 from emp_rank;
 

-- with절 사용 후, 버퍼 갯수 줄어듦
SELECT * FROM table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

-- 튜닝 전: 
select *
 from ( select ename, sal, dense_rank() over (order by sal desc) 순위
                   from emp ) 
 where  sal > ( select  avg(sal)
                     from ( select ename, sal, dense_rank() over (order by sal desc) 순위
                   from emp ) );
                   
SELECT * FROM table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));



-- 튜닝 후:
with emp_rank as ( 
                  select /*+ materialize */ ename, sal, dense_rank() over (order by sal desc) 순위
                   from emp
                  )
select *
 from emp_rank
 where  sal > ( select  avg(sal)
                     from emp_rank );
 
 
SELECT * FROM table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

실습4. 아래의 SQL을 실행하시오

select deptno, ename, sal, round(avg(sal) over (partition by deptno)) 부서평균
 from emp
 where sal > ( select avg(sal)
                from ( select deptno, ename, sal, round(avg(sal) over (partition by deptno)) 부서평균
                        from emp
                     )
              );

실습5. 위의 SQL을 WITH 절로 변경하시오

with dept_avg as (select deptno, ename, sal, round(avg(sal) over (partition by deptno)) 부서평균
                      from emp
                  )
select deptno, ename, sal, 부서평균
from dept_avg
where sal > (select avg(sal)
              from dept_avg);

실습7. 직업별 토탈월급들의 평균값을 출력하시오

select avg(sum(sal))
 from emp
 group by job;

실습8. 직업, 직업별 토탈월급을 출력하는데 직업별 토탈월급이 직업별 토탈월급들의 평균값보다 더 큰것만 출력하시오

select job, sum(sal)
 from emp
 group by job
 having sum(sal) > ( select avg(sum(sal))
                       from emp
                       group by job );

실습9. 위의 SQL을 with 절로 튜닝하시오

with job_sumsal as ( select job, sum(sal) 토탈월급
                      from emp
                      group by job )
select job, 토탈월급
 from job_sumsal
 where 토탈월급 > ( select avg(토탈월급)
                    from job_sumsal );

📌 with 절 사용시 주의 사항

여러 with절이 동시에 돌면서 다같이 느려지지 않게 하기 위해서 DBA 가 with절을 동시에 돌지 않게끔 조절해야함. 너무 느리게 도는 with절은 개발자에게 다시 작성하라고 권고해야함

with job_sumsal as  (select /*+ materialize */ job, sum(sal) as 토탈월급
                       from emp
                       group by job )
select job, 토탈월급
 from job_sumsal
 where  토탈월급 > ( select avg(토탈월급)
                      from job_sumsal );
                      
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

with job_sumsal as  (select /*+ inline */ job, sum(sal) as 토탈월급
                       from emp
                       group by job )
select job, 토탈월급
 from job_sumsal
 where  토탈월급 > ( select avg(토탈월급)
                      from job_sumsal );
                      
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));