CTE

CTE(공통 테이블 식)이란?

 

임시로 이름이 지정된 논리적인 결과셋들을 CTE(공통 테이블 식)라고 합니다.

CTE을 만들기 위해 사용되는 구문인 WITH AS를 소개합니다. 듣기만하면 쓸모없어 보이지만 외외로 활용도가 상당합니다.

원하는 쿼리의 결과를 CTE에 저장하여 다른 쿼리에 활용하기도 하며, 재귀 쿼리에도 CTE이 사용됩니다.

무엇보다 급하게 테스트할 테이블을 가지고 싶다면 CREATE ~ INSERT 와 같은 번거로운 쿼리들을 날리지 않아도 됩니다.

 

이제 사용방법을 알아봅시다.

WITH 
{alias} AS ( {query} ),
{alias} AS ( {query} ),
...

 

만든 CTE는 다른 쿼리에서 활용이 가능합니다.

간단하게 테스트용 테이블을 만들고 이를 활용하는 예제를 작성해봅시다.

WITH TEMP AS(
    SELECT 1 AS ID, '짱구' AS NAME
    UNION ALL
    SELECT 2 AS ID, '철수' AS NAME
    UNION ALL
    SELECT 3 AS ID, '맹구' AS NAME
)	-- CTE 생성
	
SELECT * FROM TEMP	-- 다른 쿼리에서 활용

 

다음과 같은 테이블이 만들어 집니다. 단 만들어진 CTE는 하나의 쿼리에서만 사용이 가능합니다.

CTE는 임시테이블과는 다릅니다. MSSQL에서 임시테이블이란 TempDB에 임시로 저장되는 테이블들을 말합니다.

하지만 CTE는 어디에도 저장되지 않고 논리적인 결과셋을 쿼리 내에서만 활용하고 있을 뿐입니다.

 

재귀쿼리에 대한 CTE 활용 

이 재귀적 CTE는 주로 계층 구조의 테이블에서 활용됩니다.

위 테이블과 같이 부모-자식 간 참조하는 관계가 존재한다면 앵커 멤버과 재귀 멤버로 나누어 재귀적인 연산이 가능합니다.

 

우선 간단한 문법부터 살펴봅시다.

WITH CTE AS(
    {쿼리} 	-- 앵커 멤버
    UNION ALL
    {쿼리} 	-- 재귀 멤버
)

SELECT * FROM CTE

재귀 CTE에의 쿼리들은 FROM절에 CTE 테이블을 포함시킬 수 있습니다.

이때 CTE 테이블은 이전에 호출된 테이블을 가르킨다. 즉 가장 먼저 호출되는 CTE는 앵커 멤버입니다.

또한 앵커 멤버에서 별칭으로 정의한 것들은 재귀 멤버에서 모두 일반 컬럼과 같이 사용 가능합니다.

 

위 테이블의 결과를 얻기 위해 앵커 멤버와 재귀 멤버를 구해봅시다.

우선 앵커 멤버의 조건은 PARENT_ID 값이 NULL입니다.

재귀 멤버는 이전 테이블의 ID를 PARENT_ID으로 설정하도록 만듭니다. 바꿔 말하자면 CTE를 조인하여 CTE의 ID을 PARENT_ID로 설정하라는 뜻입니다. 

 

그 외 재귀적으로 연산하면서 여러가지 값( DEPTH, ROOT_ID, ... )들을 얻을 수 있는데 이는 다음과 같이 얻을 수 있습니다.

WITH CTE AS(
	SELECT 
		*
		, CAST(ID AS VARCHAR) AS HIERARCHY
		, 0 AS DEPTH
		, ID AS ROOT_ID
	FROM POST_REPLIES
	WHERE PARENT_ID IS NULL
	
	UNION ALL
	
	SELECT 
		A.*
		, CONVERT(VARCHAR, HIERARCHY + '>' + CAST(A.ID AS VARCHAR)) AS HIERARCHY
		, DEPTH + 1 AS DEPTH
		, ROOT_ID
	FROM POST_REPLIES AS A
		INNER JOIN CTE AS B ON A.PID = B.ID
)


SELECT * FROM CTE ORDER BY ROOT_ID, ORD

 

 

 

참고 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/53bb2402-f107-425f-99a6-945ab4541476/where-does-cte-results-get-stored?forum=transactsql 

https://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables