[Do it! SQL 입문] 04-3. 공통 테이블 식

공통 테이블 식(Common Table Expression)은 주로 데이터베이스에 없는 테이블이 필요할 때 사용하며, 바로 다음에 실행할 SELECT 문에만 사용해야 한다는 특징이 있다. 공통 테이블 식은 목적에 따라 일반 공통 테이블 식과 재귀 공통 테이블 식으로 나뉜다. 

 

일반 CTE

 

UNION 문 과 UNION ALL 문으로 CTE 결합하기

 

INTERSECT 문으로 CTE 결합하기

 

EXCEPT 문으로 CTE 결합하기

 

 

재귀 CTE

재귀 CTE는 CTE 결과를 CTE 내부의 쿼리에서 재사용함으로써 반복 실행하는 쿼리 구조를 갖는다. 재귀 CTE는 주로 계층 데이터를 검색할 때 많이 사용한다. 

 

WITH [CTE 이름] ([열 이름1], [열 이름2]...)
AS (
	<SELECT * FROM 테이블 A>        -- 쿼리 1 (앵커 멤버)
    UNION ALL
	<SELECT * FROM 테이블 B JOIN [CTE 이름]>        -- 쿼리 2(재귀 멤버)
)
SELECT * FROM [CTE 이름]

 

재귀 CTE는 적으도 2개의 CTE 쿼리가 필요하다. 이때 각 쿼리는 앵커 멤버와 재귀 멤버를 포함해야 한다. 앵커 멤버는 자기 자신 CTE를 참조하지 않는 멤버를 의미한다. 주의할 점은 앵커 멤버는 1번째 재귀 멤버 앞에 있어야 한다는 것과, 재귀 멤버의 열 자료형은 반드시 앵커 멤버의 열 자료형과 일치해야 한다는 것이다. 또한 앵커 멤버와 재귀 멤버는 여러 개 정의할 수 있다.

 

재귀 CTE의 실행 순서

1. 최초에 쿼리 1을 실행한다. 이때 쿼리 2의 기본값은 0으로 초기화된다.
2. 이어서 쿼리 2를 실행한다. 이때 쿼리 2의 기본값은 1만큼 증가한다. 쿼리 1의 결과 행 수만큼 쿼리 2에서 CTE 이름을 재귀 호출하고, 쿼리 2의 기본값이 1씩 증가하면서 쿼리 1의 결과 행 수까지 도달해 결과가 더 없다면 재귀 호출을 중단한다.
3. 외부 SELECT 문에서 과정 1, 2를 통해 만든 CTE 누적 결과를 검색한다.