WITH 절(CTE)로 쿼리 구조화

2. 핵심 원리

2.1 WITH 절의 기본 형태

WITH 이름 AS (SELECT ...) 로 CTE 를 정의하고, 뒤따르는 하나의 문장에서 그 이름을 테이블처럼 씁니다. 쉼표로 여러 개를 이어 정의하면 뒤의 CTE 가 앞의 CTE 를 참조할 수 있습니다.

WITH a AS (
  SELECT ...
), b AS (
  SELECT ... FROM a ...
)
SELECT ... FROM b ...;

• CTE 는 그 문장 안에서만 유효합니다. 다음 문장에서는 이름이 사라지고, 뷰(중급 11 뷰)처럼 DB 에 저장되지 않습니다.
• WITH 는 WITH 뒤에 오는 SELECT 하나에 붙습니다. 정의만 하고 쓰지 않아도 오류는 아니지만 의미가 없습니다.
• 각 CTE 의 컬럼 이름은 그 안쪽 SELECT 의 별칭입니다. WITH a (x, y) AS (...) 처럼 이름 목록을 앞에 둘 수도 있습니다.

2.2 인라인 뷰·뷰와의 차이

CTE 는 "이 쿼리 한 번만 쓰는 임시 뷰" 로 생각하면 됩니다. 여러 문장이 공유하는 정의라면 뷰로, 한 문장에서 단계를 나누는 용도라면 CTE 로 만듭니다.

2.3 옵티마이저와 CTE

CTE 를 만나면 옵티마이저는 이를 본문에 인라인(병합)할지, 임시 결과로 한 번 만들어 재사용할지 정합니다. 여러 번 참조되는 CTE 는 임시 결과로 만들어질 수 있고, 인라인이 되면 인라인 뷰와 같은 계획이 됩니다. Oracle 은 여러 번 참조되면 임시 테이블로 만들 수 있고, 힌트로 이 선택을 조정할 수 있습니다.

CTE 로 바꾼다고 성능이 저절로 좋아지는 것은 아닙니다. 가독성과 재사용을 위한 문법이고, 실행 계획은 DB 마다 다르니 느린 쿼리는 실행 계획으로 확인합니다.

2.4 DB 별 지원

Tibero 도 Oracle 처럼 WITH 절을 지원합니다. MSSQL 은 WITH 가 배치의 첫 문장이 아니면 앞 문장이 세미콜론으로 끝나야 해서, ;WITH c AS (...) 로 쓰는 습관이 생겼습니다.

핵심

CTE 는 한 문장 안에서만 유효한 이름 붙은 중간 결과입니다. 인라인 뷰 중첩을 위에서 아래로 읽히게 바꾸고, 같은 결과를 여러 번 참조하게 해 줍니다.

주의

MySQL 5.7 이하에는 WITH 가 없고, MSSQL 은 앞 문장을 세미콜론으로 끝내야 합니다. DML 과 조합하는 자리도 DB 마다 달라서, H2 는 Oracle 형태(INSERT ... WITH ... SELECT)만 실행합니다.