CTE(Common Table Expressions)
SQL 쿼리에서 임시적으로 사용할 수 있는 이름을 가진 쿼리 결과 세트 즉, CTE는 다른 쿼리에서 참조할 수 있는 일시적인 테이블이다.
CTE는 WITH 키워드를 사용하여 정의된다. WITH 절 다음에는 CTE 이름과 CTE에 사용될 SELECT 문이 온다. SELECT 문에서 CTE 이름을 사용해 CTE의 결과를 참조할 수 있다.
CTE와 비교할 대상으로 VIEW가 있다. VIEW 는 만들기 위해서는 권한이 필요하고 사전에 정의를 해야한다. 하지만 CTE는 권한이 필요 없고 하나의 쿼리문이 끝날 때까지만 지속되는 일회성 테이블이다.
CTE에는 재귀적 CTE와 비재귀적 CTE가 있다.
재귀적 CTE
최초 CTE가 반복적으로 실행되어, 전체 결과 집합을 얻을 때까지 데이터의 하위 집합을 반환하는 CTE이다.
재귀적 CTE를 참조하는 쿼리를 재귀 쿼리라고 하며,
재귀 쿼리의 일반적인 용도는 계층적 데이터를 반환하는 것이다.
재귀적 CTE의 구조
T-SQL의 재귀적 CTE 구조는 다른 프로그래밍 언어의 재귀 루틴과 아주 흡사하지만.
다른 언어의 재귀 루틴은 스칼라 값을 반환하는 것이 비해, 재귀적 CTE는 여러 행을 반환할 수 있다.
재귀적 CTE는 다음 세 가지 요소로 구성된다.
1) 루틴의 호출
재귀적 CTE의 첫 번째 호출은 UNION ALL, UNION, EXCEPT 또는 INTERSECT 연산자로 조인된
하나 이상의 CTE_query_definitions로 구성된다.
이러한 쿼리 정의는 CTE 구조의 기본 결과 집합을 형성하기 때문에 앵커(Anchor) 멤버(AM)라고 한다.
CTE_query_definitions는 CTE 자체를 참조하지 않는 경우 앵커 멤버로 간주된다.
모든 앵커 멤버 쿼리 정의를 첫 번째 재귀 멤버 정의 앞에 배치하고,
UNION ALL 연산자를 사용하여 마지막 앵커 멤버를 첫 번째 재귀 멤버와 조인해야 한다.
2) 루틴의 재귀 호출
재귀 호출에는 CTE 자체를 참조하는 UNION ALL 연산자로 JOIN(조인)된 하나 이상의 CTE_query_definitions가 포함된다.
(JOIN에 대해선 차후 자세히 별도의 문서로 작성하겠다)
이러한 쿼리 정의를 재귀(Recursive) 멤버(RM)라고 한다.
3) 종료 확인
종료 확인은 암시적으로 수행되며, 이전 호출에서 반환되는 행이 없을 때 재귀가 중지된다.
CTE 장점 :
- 쿼리를 더욱 가독성있게 만들 수 있다.
- 하나의 복잡한 쿼리를 여러 개의 작은 쿼리로 분할할 수 있다.
- 같은 CTE를 여러 번 참조하더라도 데이터베이스가 해당 CTE를 한 번만 계산하므로 성능상의 이점이 있을 수 있다.
WITH RECURSIVE
CTE에서 재귀적으로 쿼리를 실행할 수 있는 구문 중 하나.
WITH RECURSIVE cte_name (column_list) AS (
-- non-recursive term
SELECT ...
UNION
-- recursive term
SELECT ...
FROM cte_name
WHERE ...
)
SELECT ...
FROM cte_name;WITH RECURSIVE 는 두 개의 부분으로 구성된다. 첫 번째 부분은 재귀적으로 실행되지 않는 초기 쿼리 부분(non-recursive term), 두 번째 부분은 재귀적으로 실행되는 쿼리 부분(recursive term).
recursive term은 다음과 같은 구조를 가진다.
SELECT ...
FROM cte_name
WHERE ...recursive term 에서는 CTE의 이름을 참조하고 WHERE 절에서는 재귀 종료 조건을 정의한다.
위 코드에서 CTE의 이름은 cte_name으로 정의했고, column_list는 CTE에서 반환할 열의 이름과 데이터 유형을 정의한다. SELECT 문에서 반환하는 열의 이름과 데이터 유형이 column_list와 일치해야 한다.
WITH RECURSIVE를 사용하면 재귀적으로 쿼리를 실행할 수 있으며, 재귀적으로 실행되는 쿼리에서는 CTE의 이름을 참조한다. 재귀적으로 실행되는 쿼리는 non-recursive term에서 반환한 결과와 recursive term에서 반환한 결과를 결합한 결과를 반환한다.
