[MySQL] 공통 테이블 식

공통 테이블 식의 특징

• 주로 데이터베이스에 없는 테이블이 필요할 때 사용한다.
• 바로 다음에 실행할 SELECT 문에만 사용해야 한다.

 

일반 CTE

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

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

CTE의 사용 방법은 WITH 문을 입력한 다음에 CTE_테이블 이름과 같이 사용할 테이블 이름을 지정하고 그 다음에 (열 이름1, 열이름2,...)와 같이 열 목록을 정의한다. 열 목록에 입력할 열 이름들은 AS(...)에 포함한 <SELECT 문>이 반환하는 열을 의미한다.

 

 

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

 

 중복을 제거한 결과를 보고 싶다면 UNION 문을 사용한다. 하지만 중복 데이터를 제거하는 연산을 포함하는 UNION 문 대신 UNION ALL 문을 사용하는 것이 좋다. UNION 문은 성는 문제를 일으키기 쉬우므로 잘 사용하지 않는 것이 좋다.

 

 

UNION ALL 문을 사용하기 전에는 ?

보통 UNION 문과 UNION ALL 문은 다른 쿼리로 데이터를 어느 정도 거른 다음 사용하는 경우가 많으므로 UNION ALL 문의 사용 빈도가 더 높다. 되도록이면 다른 쿼리에서 중복을 제거한 다음 UNION ALL 문을 사용하기를 권한다.

 

 

INTERSECT 문으로 CTE 결합하기

• 내부 조인과 비슷하지만, 내부 조인의 경우 테이블 사이의 조인 조건에 맞는 데이터를 반환하고 INTERSECT 문은 각 쿼리에서 반환한 결과에서 중복 결과를 걸러내 반환한다는 차이점이 있다.

 

 

재귀 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 누적 결과를 검색한다.