CTE랑 친해지기: WITH

프로그래밍에서는 코드 조각을 따로 빼서 이름을 붙이고 함수로 만들 수 있잖아. CTE도 딱 그 느낌이야. SELECT 서브쿼리를 메인 쿼리에서 분리해서 이름을 붙이고, 그걸 메인 SQL 쿼리에서 쓸 수 있어.

CTE(Common Table Expressions, 즉 공통 테이블 표현식)는 중첩 쿼리에 질린 개발자에게 신선한 공기 같은 존재야. SQL 코드를 더 읽기 쉽게 만들고, 진짜 우아하게 만들어줘. 예전엔 복잡한 서브쿼리 구조에 머리 아팠다면, 이제 CTE의 "마법"을 만나볼 때야.

집을 짓는다고 상상해봐. 보통은 창문부터 달고, 문부터 붙이고 싶잖아(즉, 바로 서브쿼리부터 막 쓰고 싶지). 근데 벽도 안 세웠는데 그러면 안 되지. CTE는 달라: 먼저 깔끔하게 설계도부터 그려(임시 테이블을 만드는 거지). 그리고 한 단계씩 쿼리의 층을 쌓아가. 멋지고, 안정적이고, 기술적으로도 좋아.

결국 CTE는 SELECT 쿼리로 즉석에서 만드는 가상 테이블이야. 서브쿼리랑 비슷하지만 더 멋져. 프로그래밍에서 로직을 함수로 빼서 이름 붙이듯, SQL에서는 그 역할을 CTE가 해. SELECT를 쓰고, 이름을 붙이고 — 그리고 그걸 복잡한 쿼리의 한 부분처럼 계속 쓸 수 있어. 멋지지? 완전 멋져.

SQL 서브쿼리 예시:

-- 메인 쿼리
SELECT *
FROM (
    SELECT *
    FROM 학생들
    WHERE 점수 > 75
) AS 필터된_학생들; -- 서브쿼리에 필터된_학생들이란 별칭을 줬어

서브쿼리를 따로 빼보자:

-- CTE/서브쿼리, 필터된_학생들이란 별칭을 줬어
WITH 필터된_학생들 AS (
    SELECT *
    FROM 학생들
    WHERE 점수 > 75
)

-- 메인 쿼리
SELECT *
FROM 필터된_학생들;

놀랍게도, 서브쿼리가 CTE보다 20년이나 먼저 나왔어! SQL-89 표준에 이미 서브쿼리가 있었고, CTE는 SQL-2009 표준에서야 등장했지.

WITH 문법

CTE는 WITH 키워드로 시작하고, 대충 이런 식이야:

WITH cte_이름 AS (
    SELECT ... -- 여기에 네 쿼리
)

SELECT ...
FROM cte_이름;

여기서:

• cte_이름 — 네 CTE 이름이야. high_scores, filtered_data, best_students처럼 의미 있는 이름을 아무거나 써도 돼.
• 괄호 () 안에는 나중에 쓸 데이터를 준비하는 쿼리를 넣어.
• CTE를 정의한 뒤에는, 메인 쿼리에서 일반 테이블처럼 쓸 수 있어.

예제 1: 간단한 CTE

CTE가 실제로 어떻게 동작하는지 예제로 보자. 학생들 테이블이 있다고 해보자 — 학생 리스트랑 점수가 들어있어:

목표 — 점수가 85점 넘는 학생들만 골라서 보여주기.

CTE 없이 하는 방법:

서브쿼리로 이렇게 할 수 있어:

SELECT *
FROM (
    SELECT *
    FROM 학생들
    WHERE 점수 > 85
) AS 필터된_학생들;

근데 CTE로 하면 훨씬 보기 좋아:

WITH 필터된_학생들 AS (
    SELECT *
    FROM 학생들
    WHERE 점수 > 85
)
SELECT *
FROM 필터된_학생들;

확실히 더 깔끔하고 이해하기 쉽지? 데이터 준비(WITH)랑 메인 쿼리(SELECT)를 딱 분리했어. 마치 책상 정리하고 일 시작하는 느낌 — 숨통이 트이지.

예제 2: 여러 개의 CTE

한 쿼리 안에 여러 CTE를 정의할 수도 있어. 데이터 준비를 단계별로 해야 할 때 특히 유용해.

예시: 점수들 테이블이 있고, 학생별로 과목 점수가 들어있어:

문제: 각 학생의 평균 점수를 구하고, 그 중에서 85점 넘는 학생만 고르기.

여러 CTE로 푸는 방법:

WITH 학생_평균 AS (
    SELECT 학생_id, AVG(점수) AS 평균_점수
    FROM 점수들
    GROUP BY 학생_id
),
고득점자 AS (
    SELECT 학생_id, 평균_점수
    FROM 학생_평균 	-- 첫 번째 CTE - 학생_평균을 참조
    WHERE 평균_점수 > 85
)

SELECT *
FROM 고득점자; -- 두 번째 CTE - 고득점자를 참조

여기서:

1. 학생_평균이 학생별 평균 점수를 준비해.
2. 고득점자는 그 중에서 85점 넘는 학생만 골라.

CTE와 서브쿼리의 차이

스포일러: CTE가 서브쿼리를 완전히 대체하진 않아. 근데 어떤 상황에선 훨씬 편해.

서브쿼리는 쿼리 안에 또 쿼리가 들어가는 거야. 빠르게 결과를 얻고 싶을 땐 좋아. 근데 많아지면 코드가 난장판이 돼.

예시:

SELECT *
FROM (
    SELECT 학생_id, AVG(점수) AS 평균_점수
    FROM 점수들
    GROUP BY 학생_id
) AS 학생_평균
WHERE 평균_점수 > 85;

서브쿼리는 SELECT, FROM, WHERE, HAVING 안에 다 들어갈 수 있어. 그리고 외부 쿼리 컬럼도 참조할 수 있지. CTE는 이 부분이 좀 약해.

반면에 CTE는 코드를 훨씬 읽기 쉽게 만들어줘서, 유지보수도 쉽고 버그도 줄어. 쿼리를 중첩하지 않고, 서브쿼리 결과에 이름을 붙여서 계속 쓸 수 있거든.

WITH 학생_평균 AS (
    SELECT 학생_id, AVG(점수) AS 평균_점수
    FROM 점수들
    GROUP BY 학생_id
)

SELECT *
FROM 학생_평균
WHERE 평균_점수 > 85;

CTE는 한 쿼리 안에서 준비한 데이터를 여러 번 써야 할 때 특히 좋아.

언제 CTE를 써야 할까?

• 복잡한 쿼리를 여러 단계로 나누고 싶을 때.
• 쿼리가 읽기 쉽고, 유지보수하기 쉬워야 할 때. 아무도 스파게티 코드 같은 중첩 구조를 파고들고 싶진 않잖아.
• 현재 쿼리에서만 쓸 임시 데이터를 준비할 때.

마지막 예제: 과목 분석

지금까지 배운 걸 다 합쳐보자:

1. 평균 점수가 높은 학생을 찾자.
2. 그 학생들의 이름과 수강 과목을 출력하자.

WITH 학생_평균 AS (
    SELECT 학생_id, AVG(점수) AS 평균_점수
    FROM 점수들
    GROUP BY 학생_id
),
고득점자 AS (
    SELECT 학생_id
    FROM 학생_평균
    WHERE 평균_점수 > 85
),
학생_과목 AS (
    SELECT e.학생_id, c.과목_이름
    FROM 수강신청 e
    JOIN 과목들 c ON e.과목_id = c.과목_id
)

SELECT ha.학생_id, sc.과목_이름
FROM 고득점자 ha
JOIN 학생_과목 sc ON ha.학생_id = sc.학생_id;

구조가 얼마나 깔끔한지 봐봐:

1. 먼저 평균 점수를 준비하고,
2. 그 다음에 고득점자만 골라,
3. 그리고 그 학생들을 과목이랑 연결해.

이제 너도 공식적으로 CTE를 써서 멋지고, 읽기 쉽고, 강력한 SQL 쿼리를 만들 준비가 된 거야.

이제 네 프로젝트에서 직접 써보자!