[DB] CTE (a.k.a WITH절)

🔍 들어가기 전에

---

최근에 WITH절과 임시테에블의 차이를 찾다가 문득 CTE라는 개념을 접하게 되었다.
CTE는 무엇이며, WITH절과 CTE는 무슨 관계가 있는걸까?
오늘 포스팅에서 자세히 알아보자.

📌 CTE

---

CTE는 Common Table Expressions의 약자이다. CTE는 SELECT, INSERT , UPDATE, DELETE에서 참조가 가능한 임시의 결과값이다.

조금 더 풀어 설명하면, SELECT, INSERT , UPDATE, DELETE 쿼리를 실행할 때, 해당 쿼리 내부에서 CTE를 사용할 수 있으며, 이는 테이블처럼 영구적으로 저장되는게 아닌 쿼리가 실행되는 동안에만 임시로 존재하는 결과값이다. 또한 CTE는 임시 결과값이기 때문에 항상 결과값을 반환한다.

CTE는 크고 복잡한 쿼리를 단순화시키는데 유용하며, 위계 또는 계층이 있는 데이터를 조작하는데 이점이 있다.

종류로는 아래 두 가지가 있다.

• 재귀적 CTE (Recursive)
• 비재귀적 CTE (Non-Recursive)

이번 포스팅은 CTE의 전반적인 내용에 대해 알아보기 위해 비재귀적 CTE를 주로 다루고자 한다.

CTE는 WITH절을 통해 정의할 수 있으며, 사용 방법은 아래에서 자세히 다뤄보고자 한다.

📌 WITH절

---

WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

CTE를 정의하는 방법은 아래와 같다.

• DML 쿼리 전에 정의
• WITH 키워드로 시작
• 사용할 이름으로 CTE 명명
• CTE 사용 시 필요한 컬럼들 정의 (선택사항)
• AS 키워드
• 필요한 쿼리문 작성

CTE를 정의하고나면, DML에서 원하는 위치, 원하는 만큼 여러번 사용 가능하다.

📌 특징

---

• 테이블처럼 영구적으로 저장되는 것이 아닌, 쿼리 실행할 때에만 임시적으로 존재
• 쿼리 가독성 증가
• 쿼리 단순성 증가
• 하나의 쿼리에서 여러번 사용 가능
• 재귀적으로 원하는 결과값에 접근 가능
  ex) 댓글에 댓글

📌 활용 예제

---

• 테이블 구성

# 학생 테이블
CREATE TABLE STUDENT_MAST (
	STUDENT_ID VARCHAR(30) PRIMARY KEY
,	STUDENT_NAME VARCHAR(10)
,	CLASS_ID VARCHAR(30)
);

# 교실 테이블
CREATE TABLE CLASS_ROOM_MAST (
	CLASS_ID VARCHAR(30) PRIMARY KEY
,	CLASS_NAME VARCHAR(10)
);

# 성적 테이블
CREATE TABLE EXAM_GRADE (
	STUDENT_ID VARCHAR(30) PRIMARY KEY
,	GRADE INT
);

# 교실 데이터 세팅
INSERT INTO CLASS_ROOM_MAST (CLASS_ID, CLASS_NAME) VALUES ('C01', '1반');
INSERT INTO CLASS_ROOM_MAST (CLASS_ID, CLASS_NAME) VALUES ('C02', '2반');
INSERT INTO CLASS_ROOM_MAST (CLASS_ID, CLASS_NAME) VALUES ('C03', '3반');

# 학생 데이터 세팅
INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S01', '강동원', 'C01');
INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S02', '전지현', 'C01');
INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S03', '이동욱', 'C01');
INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S04', '육성재', 'C01');
INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S05', '신민아', 'C01');

INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S06', '표지훈', 'C02');
INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S07', '이제훈', 'C02');
INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S08', '한소희', 'C02');
INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S09', '김강우', 'C02');
INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S10', '한가인', 'C02');

INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S11', '주윤발', 'C03');
INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S12', '장국영', 'C03');
INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S13', '김또가', 'C03');
INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S14', '가진동', 'C03');
INSERT INTO STUDENT_MAST (STUDENT_ID, STUDENT_NAME, CLASS_ID) VALUES ('S15', '왕대륙', 'C03');

# 성적 데이터 세팅
INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S01', 81);
INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S02', 90);
INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S03', 75);
INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S04', 100);
INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S05', 87);

INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S06', 98);
INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S07', 70);
INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S08', 71);
INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S09', 65);
INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S10', 90);

INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S11', 60);
INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S12', 70);
INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S13', 100);
INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S14', 87);
INSERT INTO EXAM_GRADE (STUDENT_ID, GRADE) VALUES ('S15', 73);

# 모든 테이블 정리
# DROP TABLE STUDENT_MAST, CLASS_ROOM_MAST, EXAM_GRADE;

• 반별 성적 1등 학생 구하는 쿼리 (기본형)

SELECT	VSM.CLASS_NAME
	,	VSM.STUDENT_NAME
    ,	VSM.GRADE
FROM (
	SELECT	CRM.CLASS_NAME
		,	CRM.CLASS_ID
		,	SM.STUDENT_ID
		,	SM.STUDENT_NAME
		,	ER.GRADE
		,	RANK() OVER(PARTITION BY SM.CLASS_ID ORDER BY ER.GRADE DESC) AS RANK_BY_CLASS
	FROM STUDENT_MAST SM
	INNER JOIN CLASS_ROOM_MAST CRM
		ON SM.CLASS_ID = CRM.CLASS_ID
	INNER JOIN EXAM_GRADE ER
		ON SM.STUDENT_ID = ER.STUDENT_ID
) VSM
WHERE RANK_BY_CLASS = 1
ORDER BY VSM.CLASS_ID ASC
;

• 반별 성적 1등 학생 구하는 쿼리 (CTE)

# CTE 정의
WITH STUDENT_RANK AS (
	SELECT	CRM.CLASS_NAME
		,	CRM.CLASS_ID
		,	SM.STUDENT_ID
		,	SM.STUDENT_NAME
		,	ER.GRADE
		,	RANK() OVER(PARTITION BY SM.CLASS_ID ORDER BY ER.GRADE DESC) AS RANK_BY_CLASS
	FROM STUDENT_MAST SM
	INNER JOIN CLASS_ROOM_MAST CRM
		ON SM.CLASS_ID = CRM.CLASS_ID
	INNER JOIN EXAM_GRADE ER
		ON SM.STUDENT_ID = ER.STUDENT_ID
)
# CTE 사용
SELECT	SR.CLASS_NAME
	,	SR.STUDENT_NAME
    ,	SR.GRADE
FROM STUDENT_RANK SR
WHERE SR.RANK_BY_CLASS = 1
ORDER BY SR.CLASS_ID ASC
;

• 쿼리 결과
  

✍🏻 오늘의 정리

---

• CTE는 Common Table Expressions
• 임시의 결과값
• WITH절을 사용해 CTE 정의
• CTE의 특징

🔗 참고·출처

---

• Common Table Expressions: When and How to Use Them - Chartio
• Common Table Expressions : The Ultimate Guide
• WITH (Common Table Expressions) -MySQL

👀 스스로 피드백

---

• CTE와 임시 테이블의 차이에 대해 알아보기
• 재귀적 CTE (Recursive)의 개념에 대해서 알아보기
• Derived Table이란 무엇인지