[MSSQL] CTE 문법 정리

MSSQL를 공부하다보면 CTE, MERGE, OUTPUT, APPLY와 같은 문법을 볼 수 있다. CTE는 재귀적으로 사용하면 계층적인 데이터를 반환할 수 있기 때문에 어느 정도 익숙할 수 있지만 MERGE, OUTPUT, APPLY 구문은 익숙하지 않을 수 있기 때문에 이 문법의 사용 방법을 몇 개의 포스팅에 나눠 정리하려고 한다. 

 

CTE

Common Table Expression의 약자로 "공통 테이블 식"이라고도 부른다. 실제로 CTE라는 키워드가 코드 안에 들어가는 것은 아니고, WITH 구문을 사용한다. CTE는 SELECT문을 미리 정의해 이름을 붙인 후, 이어지는 쿼리에서 테이블처럼 사용하는 기능을 말한다. CTE는 바로 다음에 오는 SELECT절에만 해당 CTE를 이용할 수 있다.

CTE는 목적에 따라 일반 CTE와 재귀 CTE로 나눌 수 있는데, 각각 살펴보자.

 

일반 CTE

정의

• 지정된 결과 집합을 임시 이름으로 지정한다.

• 단순 쿼리에서 파생되며 하나의 SELECT, INSERT, UPDATE, DELETE, MERGE문만 정의할 수 있다.

• CREATE VIEW의 SELECT문 정의의 일부로 CTE를 사용할 수 있다.

특징

• derived table(=inline view)과 같이 개체로 저장되지 않고 쿼리 실행 기간 동안만 존재한다.

  하지만 derived table과 다르게, CTE는 같은 쿼리 내에 self 참조와 multiple 참조가 가능하다.

• 사용자 지정 함수, 저장 프로시저, 트리거, 뷰와 같은 사용자 정의 루틴에 정의될 수 있다.

사용처

• Recursive 쿼리 생성 (같은 쿼리 내에 self 참조하는 경우)

• view의 역할 대체 (metadate에 정의를 저장할 필요가 없을 경우 view대신 CTE 사용)

장점

• CTE를 사용하면 코드가 단순해져 가독성이 향상된다. 이에 따라 복잡한 쿼리를 쉽게 유지 & 관리할 수 있다.

• 쿼리를 개별적인 논리적 구성 블록으로 나눌 수 있고, 이 블록을 사용해서 복잡한 중간 CTE를 작성할 수 있다.

기본 구문

WITH expression_name [(column_name [, ...n])] 
AS (
    CTE_query_definition
)

 

• CTE_query_definition에 모든 결과 열에 대한 이름이 있는 경우에만 column_name 생략이 가능하다.
• 일괄처리에 CTE가 속한 경우, 그 앞 쿼리문은 세미콜론(;)으로 종료해야 한다.

 

재귀 CTE

정의

최초 CTE가 반복적으로 실행되어 전체 결과 집합을 얻을 때까지 데이터의 하위 집합을 반환하는 CTE이다.

용도

계층적 데이터를 반환하기 위한 목적으로 사용된다.

구조

앵커 멤버(Anchor member)

• 재귀 CTE의 첫 번째 호출로, 집합 연산자로 연결된 하나 이상의 '쿼리 정의'(CTE_query_definition)로 구성된다.

• 다른 CTE를 참조하지 않는 경우 앵커 멤버로 간주한다.

• 모든 앵커 멤버의 '쿼리 정의'를 UNION ALL로 하나로 묶고, 묶인 쿼리를 첫 번째 재귀 멤버 '쿼리 정의' 앞에 두고 UNION ALL로 연결해야 한다.

재귀 멤버(Reculsive member)

• CTE를 참조하는 UNION ALL로 연결된 하나 이상의 '쿼리 정의'로 구성된다.

이전 호출에서 반환되는 행이 없을 때 재귀를 암묵적으로 종료한다.

특징

재귀 CTE에서 무한 루프가 발생할 경우, OPTION절에 MAXRECURSION 값을 0~32767 사이의 값으로 지정하여 최대 반복 횟수를 지정할 수 있다. 기본 값은 100이며, 0으로 지정하면 최대 반복 횟수가 없어 무한으로 반복된다.

사용 예시

-- 재귀 CTE
CREATE TABLE #test_tbl(
   EmpID smallint NOT NULL,
   MngID smallint NULL
);
INSERT INTO #test_tbl 
   VALUES (1, NULL), (273, 1), (274, 273), (285, 273), (16, 273), (275, 274), (276, 274), (286, 285), (23, 16);

WITH cte_emp
AS (
-- 앵커 멤버
SELECT EmpID, MngID, 0 Level, CAST('' AS varchar(100)) mngList
   FROM #test_tbl
   WHERE MngID IS NULL    --> 시작 조건
UNION ALL
-- 재귀 멤버
SELECT a.EmpID, a.MngID, b.Level + 1, CAST(' -> ' + CAST(a.MngID AS varchar(3)) + b.mngList AS varchar(100))
   FROM #test_tbl a
   INNER JOIN cte_emp b
   ON a.MngID = b.EmpID   --> 재귀 조건
   )
SELECT *
   FROM cte_emp
;

위 구문을 실행하면 아래와 같은 결과가 나온다.

 

예시 쿼리 실행 결과 테이블

예시 계층 구조 도식화

 

위의 쿼리는 아래 그림처럼 Level마다 테이블 결과가 반환돼서 UNION ALL로 합쳐져 나온 결과이다.

 

결과 테이블이 만들어지는 과정

 

여기까지 CTE 사용법을 정리해 보았고, 다음 글에서는 MERGE 문법의 사용법과 동작 방법에 대해 다룰 것이다.