WITH를 사용한 임시 테이블 만들기

임시 테이블은 중간 결과를 저장해서 나중에 편하게 다룰 수 있는 꿀팁이야. 프로그래밍에서 무거운 코드를 계속 복붙하는 대신, 변수에 한 번 저장해두고 쓰는 거랑 비슷하지. SQL 세계에서 이런 "변수" 역할을 임시 테이블이 해줘.

근데 CTE는 이걸 더 쉽게 만들어줘 — 귀찮은 과정 없이 임시 테이블을 바로 쓸 수 있거든:

• 삭제 걱정 없음.

  CTE는 쿼리 실행되는 순간에만 살아있어. SQL이 끝나면 CTE도 바로 사라져. 마치 보이지 않는 어시스턴트가 다 해주고 흔적도 없이 사라지는 느낌!

• 코드가 더 깔끔해져.

  CTE 만드는 게 물리적인 임시 테이블 만들고 지우는 것보다 훨씬 간단해. 한눈에 다 보이고, 쓸데없는 과정도 없어.

• "한 번만 쓰는" 작업에 딱이야.

  딱 한 번 데이터 처리만 하면 된다면 — CTE가 완전 제격! 빠르고, 깔끔하고, 부작용도 없어.

임시 테이블로서의 CTE 문법

먼저 CTE 문법부터 다시 보자:

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

여기서 WITH는 쿼리 안에서만 쓸 수 있는 임시 "테이블 표현식"을 만들어줘. 이 표현식은 테이블처럼 보이지만, 쿼리 끝나면 바로 사라져.

예시: 우수생 찾기

CTE로 임시 테이블을 만들어서, 평균 점수(grade)가 90점 넘는 학생들을 찾아보자. 그리고 그 명단을 출력해볼게.

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

여기서 우리는:

• WITH로 high_achievers라는 임시 테이블을 만든다.
• CTE 안에서 각 학생(student_id)별로 점수(grades)를 그룹화하고, 평균 점수를 계산한다.
• SELECT * 쿼리에서 그냥 high_achievers 임시 테이블을 일반 테이블처럼 쓴다.

CTE와 임시 테이블 비교

가끔 이런 질문이 나와: CREATE TEMP TABLE로 만든 임시 테이블이랑 CTE 쓰는 거랑 뭐가 달라?

같은 데이터를 위한 전통적인 임시 테이블(CREATE TEMP TABLE) 예시는 이래:

CREATE TEMP TABLE high_achievers_temp AS
	SELECT student_id, AVG(grade) AS avg_grade
	FROM grades
	GROUP BY student_id
	HAVING AVG(grade) > 90;

SELECT *
FROM high_achievers_temp;

DROP TABLE high_achievers_temp;  -- 테이블 삭제하는 거 잊지 마!

그리고 CTE로 똑같은 쿼리:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

언제 CTE를 쓰고, 언제 임시 테이블을 쓸까?

CTE는 중간 데이터를 빠르게 만들고 바로 처리하고 싶을 때 완전 좋아 — 귀찮은 과정 없이! 삭제도 신경 쓸 필요 없고, 쿼리 구조 안에서 바로 보여서, 임시 테이블처럼 따로 숨겨져 있지 않아. 그래서 쿼리가 더 깔끔하고, 이해하기 쉽지. 특히 여러 단계로 데이터 처리할 때 더 좋아. 그리고 CTE는 여러 개를 조합하거나, 중첩해서 복잡한 로직도 만들 수 있어 — 이건 나중에 더 얘기할게.

이런 구조는 쿼리가 한 번만 쓰이고, 데이터도 그 쿼리 안에서만 필요할 때 완전 잘 맞아. 근데 결과를 여러 번, 시스템의 다른 부분에서 계속 써야 하거나, 중간 데이터를 세션 내내 유지하고 싶으면 — 임시 테이블이 더 믿음직한 선택이야. 특히 데이터 양이 많고, 성능이 중요할 때는 물리적인 임시 테이블이 더 빠르고 안정적이야.

결국 상황에 따라 달라: CTE는 빠르고, 우아하게 로컬 데이터 처리할 때 쓰는 도구! 임시 테이블은 좀 더 복잡하고 오래가는 시나리오에 쓰는 일꾼이지.

예시: 데이터 집계하기

예를 들어, enrollments라는 테이블에 어떤 학생이 어떤 강좌에 등록했는지 기록되어 있다고 해보자. 각 강좌별로 등록한 학생 수를 알고 싶은데, 학생이 5명 넘는 강좌만 보고 싶어.

CTE로 하면 이렇게 돼:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
    HAVING COUNT(student_id) > 5
)

SELECT *
FROM course_counts
ORDER BY student_count DESC;

여러 개의 CTE: 단계 쪼개기

만약 작업을 여러 단계로 나누고 싶으면? 예를 들어, 먼저 평균 점수 높은 학생을 뽑고, 그 다음에 그 학생들의 강좌를 찾고 싶다? 완전 쉽지!

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

이제 쿼리에 CTE가 두 개야:

1. high_achievers가 우수생을 찾고,
2. student_courses가 학생과 강좌를 매칭해.

결과는 — 평균 점수 높은 학생들과 그 학생들이 듣는 강좌 리스트!

CTE 쓸 때 흔한 실수

데이터가 너무 많을 때. CTE는 PostgreSQL 메모리에서 만들어져. 결과가 엄청 크면 쿼리가 느려지거나 메모리 한도 초과될 수 있어.

남용. 그냥 서브쿼리가 더 간단할 때 CTE를 억지로 쓰면 오히려 헷갈릴 수 있어.

인덱스 깜빡함. CTE에서 큰 테이블을 인덱스 없이 조회하면 쿼리가 느려져.