공통테이블식 CTE(Common Table Expression)

공통테이블식은 파생테이블과 유사하다. 어떤 쿼리의 결과를 임시 테이블로 사용하여 select, insert, update, delete 나 merge 같은 연산을 외부 쿼리에서 수행할 수 있다. 그리고 이 외부 쿼리가 종료되면 사라져 버린다. 

 

Syntax

 

With <cte_이름> [(<대상 컬럼 목록>)]

As

(

           <CTE를 정의하는 내부 쿼리>

)

<CTE에 대한 외부 쿼리>;

 

# 예제1 

CTE 이름 뒤에 컬럼을 적지 않는 것을 인라인 방식이라고 한다.

 

with C as

(

    select dept_id, dept_nm

    from DEPT

    where dept_id = '123'

)

select *

from c;

;

 

# 예제2

CTE 이름 뒤에 컬럼을 적는 것을 외부 방식이라고 한다.

 

with c (dept_id, dept_nm) as

(

    select  dept_id, dept_nm

    from DEPT

    where dept_id = '123'

)

select *

from c;

 

여러 CTE 정의하기

 

CTE를 다른 CTE에서 참조할 수 있다. A라는 With 절 다음에 쉼표로 구분된 B라는 CTE D라는 CTE등 여러 CTE를 정의할 수 있다. 각각의 CTE는 앞에 정의된 다른 CTE를 참조할 수 있다. 외부 쿼리 또한 모든 CTE를 참조할 수 있다.

 

# 예제3

with A as

(

    select div, dept_id, nm_kr

    from dept

),

B as

(

    select div, count(dept_id) as num_of_dept  -- 부서수 

    from A

    group by A.div -- 사업부별

)

 

select *

from B

 

CTE에서 다중 참조하기

동일한 CTE를 여러 인스턴스 형태로 참조해보자

 

# 예제4

with c as

(

    select up_dept_id, dept_id, dept_nm

    from dept

)

select c2.DEPT_NM as UP_DEPT_NM

     , c1.DEPT_NM

from c as c1

left join c as c2

    on c1.UP_DEPT_ID = c2.DEPT_ID

;

 

재귀CTE

재귀는 자기가 자기 자신을 호출하는 독특한 구조이다. 쿼리내에 종료 조건이 있거나 더 이상 자기 자신을 호출할 수 없는 경우가 발생하면 자동으로 종료된다.

 

#예제5 

with c (up_dept_id, dept_id, dept_nm) as

(

    select UP_DEPT_ID

         , dept_id

         , cast( dept_nm as nvarchar(max)) as Dept_nm

    from dept

    where dept_id = '20215'

    union all

    select d2.UP_DEPT_ID,

           d2.dept_id,

          cast((d2.dept_nm + ' > ' + d1.dept_nm) as nvarchar(max)) as DEPT_NM

    from dept as d2

    inner join c as d1

        on d1.up_dept_id = d2.DEPT_ID

)

select *

from c