[SQL 입문] 공통 테이블 식

공통 테이블 식

교재:  Do it! SQL 입문

174p ~ 183p

 

공통 테이블식(Common table sxpression)은 주로 데이터베이스에 없는 테이블이 필요할 때 주로 사용한다

바로 다음에 실행할 SELECT 문에만 사용해야 한다는 한다는 특징이 있다

 

공통 테이블 식은 일반 공통 테이블  식과 재귀 공통 테이블 식으로 나뉜다

일반 CTE

 CTE 안에서 UNION문, UNION ALL문, INTERSECT문, EXCEPT문을 사용해 여러 개의 일반 CTE 쿼리를 결합할 수 있다일반 CTE는 복잡한 쿼리를 단순하게 만들 때 사용하기 좋다

 

CTE의 기본 형식

WITH [CTE_테이블 이름] (열 이름1, 열 이름2, ...)
AS
(
<SELECT 문>
)
SELECT [열 이름] FROM [CTE_테이블 이름];

안에 있는 SELECT문으로 조회한 결과를 CTE 테이블에 저장 후 추후에 나오는 SELECT문을 이용해 해당 CTE 테이블에서 사용할 수 있다

단, 열 개수가 맞지 않으면 오류가 발생한다

 

UNION / UNION ALL 문으로 CTE 결합

 

CTE에서 INTERSECT 문으로 중복 결과 검색

 

CTE에서 EXCEPT 문으로 중복 제거

 

 

재귀 CTE

데이터 중복을 최소화해서 조직의 목적에 맞게 효율적으로 관리하기 위해 사용한다

재귀 CTE는 CTE 결과를 CTE 내부의 쿼리에서 재사용함으로써 반복 실행하는 쿼리 구조를 갖는다

 

재귀 CTE의 기본 형식

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

재귀CTE는 적어도 2개의 CTE쿼리가 필요하다

각 쿼리는 앵커 멤버와 재귀 멤버를 포함해야 한다

 

앵커멤버: 자기 자신을 참조하지 않는 멤버

1번째 재귀 멤버 앞에 있어야 하며 앵커 멤버의 열 자료형과 일치해야 함

 

재귀 멤버와 앵커 멤버는 여러 개 정의할 수 있다

 

재귀 CTE의 실행 순서

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