MySQL CTE WITH — 공통 테이블 식 완전 정리

 

 

Database · MySQL

MySQL CTE(WITH) —
공통 테이블 식 완전 정리

서브쿼리에 임시 이름을 붙여 본 쿼리에서 테이블처럼 재사용하는 법

시작 오늘 분석할 코드

회원별 총 구매 금액을 구해서 많이 산 순서로 정렬하는 쿼리다. 핵심은 WITH abc(...)로 가상의 임시 테이블 abc를 먼저 만들어 두고, 그 아래 본 쿼리에서 abc를 진짜 테이블처럼 갖다 쓴다는 점이다.

WITH abc(userid, total) -- abc라는 가상 테이블명을 선언 AS (SELECT userid, SUM(price*amount) FROM buyTBL GROUP BY userid ) -- buyTBL에서 합계를 구하고 userid로 그룹핑 -- CTE 정의 끝. SELECT * FROM abc ORDER BY total DESC ;

실제 결과 미리보기

userid	total
LSG	1,500,000
KBS	1,200,000
JYP	950,000
SSK	600,000
EJW	400,000

※ buyTBL의 원본 값에 따라 실제 숫자는 달라진다. 여기서는 이해를 돕기 위한 예시 데이터다. 핵심은 total 기준 내림차순으로 줄이 정렬돼 있다는 점이다.

용어 용어 정리

CTECommon Table Expression — 이름 붙은 임시 결과 집합

WITHCTE를 선언하는 SQL 예약어, 쿼리 맨 앞에 옴

공통 테이블 식CTE의 한국어 번역 — "본 쿼리가 공통으로 참조할 표"

서브쿼리쿼리 안에 들어가는 또 다른 쿼리 (CTE와 대비되는 개념)

GROUP BY지정한 컬럼 값이 같은 행끼리 하나로 묶음

SUM()그룹별로 숫자 컬럼을 모두 더하는 집계 함수

별칭(AS)결과 집합이나 컬럼에 새 이름을 붙이는 문법

ORDER BY DESC지정 컬럼 기준 내림차순(큰 값 → 작은 값) 정렬

---

1 CTE(WITH)란 무엇이고 왜 쓰는가

왜 그냥 서브쿼리를 안 쓰고 CTE를 쓰는가?

CTE(Common Table Expression, 공통 테이블 식)는 쿼리 실행 직전에 잠깐 만들어졌다가 그 쿼리가 끝나면 사라지는 임시 이름표가 붙은 결과 집합이다. 서브쿼리로도 똑같은 걸 만들 수 있지만, 서브쿼리는 FROM (SELECT ...) 임시이름 형태로 본 쿼리 안에 파고들어가야 해서 쿼리가 길어질수록 괄호가 겹겹이 쌓이고 읽기 어려워진다. CTE는 "먼저 이 표를 만들어 놓고, 그다음 이걸 갖고 이렇게 하겠다"는 순서로 위에서 아래로 읽히기 때문에 가독성이 훨씬 좋다.

※ 서브쿼리 = 쿼리 안에 숨어있는 쿼리 (안에서 밖으로 읽어야 함)
CTE = 쿼리 앞에 따로 떼어놓은 쿼리 (위에서 아래로 읽으면 됨)

---

2 WITH abc(userid, total) — CTE 이름과 컬럼 선언

괄호 안 (userid, total)은 왜 쓰는가?

WITH abc(userid, total)에서 abc는 이제부터 쓸 가상 테이블의 이름이고, 괄호 안 (userid, total)은 그 가상 테이블이 가질 컬럼 이름을 미리 정해두는 것이다. 즉 "abc라는 표를 만들 건데, 그 표는 userid 컬럼과 total 컬럼 두 개로 이뤄져 있다"고 선언하는 셈이다. 이 컬럼 이름은 실제 SELECT 결과의 원래 컬럼명(SUM(price*amount) 같은 긴 표현식)을 깔끔한 이름으로 바꿔주는 역할도 한다.

WITH abc(userid, total) └────┬────┘ └──────┬──────┘ 가상 테이블명 가상 테이블의 컬럼 목록

---

3 AS (SELECT ...) — CTE의 실제 내용 정의

AS 뒤 괄호 안은 무슨 역할인가?

AS 뒤에 오는 (SELECT ...) 부분이 abc라는 이름표가 실제로 담을 데이터다. "abc(userid, total)"이라는 껍데기 이름만 선언했다면, AS (SELECT userid, SUM(price*amount) FROM buyTBL GROUP BY userid)가 그 껍데기를 채우는 진짜 쿼리다. SELECT의 컬럼 개수(2개: userid, SUM 결과)와 앞서 선언한 컬럼 목록의 개수(2개: userid, total)가 반드시 순서대로 짝이 맞아야 한다.

※ SELECT 결과가 3개 컬럼인데 WITH abc(userid, total)처럼 2개만 선언하면 개수 불일치로 오류가 난다.

---

 

반응형

 

4 GROUP BY userid — 회원별로 묶기

GROUP BY가 없으면 왜 안 되는가?

buyTBL(구매 테이블)에는 한 회원이 여러 번 구매한 기록이 각각 한 행씩 들어있다. GROUP BY userid는 같은 userid를 가진 행들을 하나의 그룹으로 묶어주는 역할을 한다. 이 묶음이 있어야 그 다음에 나오는 SUM()이 "회원 한 명당 하나의 합계"를 계산할 수 있다. GROUP BY가 없으면 SUM은 테이블 전체 행을 다 더해버려서 회원별 합계가 아니라 전체 합계 딱 한 줄만 나온다.

GROUP BY 전: LSG-1000, LSG-2000, KBS-500, KBS-700 (4행) GROUP BY 후: LSG 그룹, KBS 그룹 (2그룹으로 압축)

---

5 SUM(price*amount) — 총 구매 금액 계산

price*amount를 먼저 곱하고 SUM하는 이유는?

price(단가)와 amount(수량)를 각 행마다 먼저 곱해서 "그 행의 구매 금액"을 만들고, SUM()이 그 값을 그룹(userid)별로 모두 더한다. SUM(price)만 쓰면 단가만 더해질 뿐 수량이 반영되지 않으므로, 곱셈이 먼저, 합계가 그다음이라는 계산 순서를 정확히 이해해야 한다.

1행: price=1000, amount=3 → price*amount = 3000 2행: price=2000, amount=1 → price*amount = 2000 SUM(price*amount) = 3000 + 2000 = 5000 (한 회원의 총 구매액)

---

6 abc를 진짜 테이블처럼 쓰기 — SELECT * FROM abc

FROM abc는 어떻게 동작하는가?

CTE 정의가 끝나면 바로 이어지는 본 쿼리 SELECT * FROM abc ORDER BY total DESC에서 abc는 마치 실제로 존재하는 테이블처럼 취급된다. buyTBL을 직접 건드리지 않고, 앞서 만들어둔 "userid별 합계 표"인 abc만 보고 정렬하면 되므로 쿼리가 두 단계로 명확히 나뉜다: ① 집계 표 만들기(WITH) → ② 그 표로 원하는 걸 하기(본 쿼리).

※ 핵심 포인트

• abc는 실제 DB에 저장되는 테이블이 아니라 이 쿼리 한 번 실행되는 동안만 존재하는 임시 결과
• 본 쿼리에서는 abc의 컬럼명(userid, total)을 그대로 사용할 수 있음
• abc에 다시 WHERE·JOIN 등을 걸어도 문제없음(진짜 테이블처럼 취급되므로)

---

7 ORDER BY total DESC — 정렬

DESC는 왜 필요한가?

ORDER BY total까지만 쓰면 기본값인 오름차순(ASC, 작은 값→큰 값)으로 정렬된다. 여기서는 "많이 산 회원이 위로" 오게 하려는 것이므로 내림차순(DESC, 큰 값→작은 값)을 명시해야 한다. total은 CTE 단계에서 이미 계산이 끝난 컬럼이므로, 본 쿼리는 복잡한 계산 없이 정렬만 담당하면 된다는 점도 CTE를 쓰는 이유 중 하나다.

ORDER BY total -- 기본 ASC: 100 → 500 → 1000 ORDER BY total DESC -- 명시 DESC: 1000 → 500 → 100

---

8 이 쿼리가 실행되는 순서

MySQL이 코드를 위에서부터 그대로 실행할까?

쿼리는 눈에 보이는 순서(WITH → SELECT)로 적혀 있지만, 내부적으로는 CTE부터 완성한 뒤 본 쿼리가 그 결과를 사용하는 순서로 처리된다. 단계별로 나눠보면 다음과 같다.

1

buyTBL 전체 스캔구매 기록 전체 행을 대상으로 읽기 시작

2

GROUP BY userid같은 userid를 가진 행들을 그룹으로 묶음

3

SUM(price*amount) 계산그룹별로 구매 금액을 모두 더해 total 산출

4

CTE abc(userid, total) 완성계산이 끝난 결과에 abc라는 이름표를 붙여 임시 저장

5

본 쿼리 SELECT * FROM abc 실행이제 막 만들어진 abc를 일반 테이블처럼 조회

6

ORDER BY total DESCabc의 total 컬럼 기준으로 내림차순 정렬 후 최종 반환

buyTBL

→

GROUP BY + SUM

→

CTE abc

→

ORDER BY DESC

→

최종 결과

※ 즉 CTE는 "먼저 계산해서 이름 붙여놓고, 그다음 그 이름으로 작업한다"는 2단계 흐름을 SQL 문법으로 그대로 표현한 것이다.

---

9 CTE vs 서브쿼리 vs VIEW — 무엇이 다른가

셋 다 "이름 붙은 결과 집합"인데 왜 구분하는가?

세 방법 모두 "쿼리 결과에 이름을 붙여 재사용한다"는 목적은 같지만, 생존 범위와 저장 여부가 다르다.

CTE (WITH)

• 그 쿼리 한 문장 안에서만 존재
• 저장 안 됨, 매번 새로 계산
• 가독성 좋고 재귀 쿼리도 가능

서브쿼리

• FROM 절 괄호 안에 중첩
• 저장 안 됨, 그 쿼리 안에서만
• 중첩 많아지면 가독성 저하

VIEW는 이 둘과 달리 CREATE VIEW로 DB에 이름과 정의가 영구 저장돼서 다른 쿼리·다른 세션에서도 계속 재사용할 수 있다. "이번 한 번만 쓸 임시 표"면 CTE, "앞으로 계속 여러 쿼리에서 쓸 표"면 VIEW를 고른다.

※ 정리: CTE·서브쿼리 = 그 쿼리 한 번만 유효 / VIEW = DB에 저장돼 계속 유효

---

10 자주 하는 실수

실수	증상	해결
WITH 뒤 컬럼 목록 개수와 SELECT 컬럼 개수 불일치	컬럼 개수 오류 또는 원치 않는 컬럼명	WITH abc(a, b)의 개수·순서를 SELECT 컬럼과 정확히 맞추기
CTE(abc)를 다른 쿼리문에서 다시 참조	"테이블 abc 없음" 오류	CTE는 정의된 그 쿼리 안에서만 유효 — 필요하면 매번 WITH로 재선언하거나 VIEW 사용
CTE 정의부와 본 쿼리 사이에 세미콜론(;) 삽입	구문 오류(Syntax Error)	WITH ~ 본 쿼리는 하나의 문장 — 세미콜론은 맨 끝에 한 번만
SUM() 등 집계 함수만 쓰고 GROUP BY 생략	회원별 합계가 아닌 전체 합계 한 줄만 나옴	집계 기준이 되는 컬럼(userid)을 GROUP BY에 명시
CTE를 여러 개 만들면서 WITH를 각각 따로 씀	두 번째 WITH부터 구문 오류	WITH a AS (...), b AS (...) 처럼 콤마로 하나의 WITH에 연결
MySQL 8.0 미만 버전에서 WITH 사용	"WITH" 근처에서 구문 오류(인식 자체를 못함)	CTE는 MySQL 8.0부터 지원 — 버전 먼저 확인

---

핵심 한 줄 요약

CTE(WITH)쿼리 한 번만 쓰고 사라지는 이름 붙은 임시 결과 집합

WITH abc(컬럼목록)가상 테이블 이름과 컬럼 이름을 먼저 선언

AS (SELECT...)그 가상 테이블에 실제로 채워질 데이터 정의

GROUP BY + SUM회원별로 묶어서 각 그룹의 합계 계산

본 쿼리 FROM abcCTE 결과를 진짜 테이블처럼 재사용

ORDER BY total DESC큰 값부터 내림차순 정렬

CTE vs VIEWCTE=1회성, VIEW=DB에 영구 저장

Tags

#MySQL #CTE #WITH절 #공통테이블식 #서브쿼리 #GROUPBY #SUM #집계함수 #가상테이블 #ORDERBY #SELECT #SQL #데이터베이스 #DB #웹개발

▼ 티스토리 태그 입력란 복사용

MySQL, CTE, WITH절, 공통테이블식, 서브쿼리, GROUP BY, SUM, 집계함수, 가상테이블, ORDER BY, SELECT, SQL, 데이터베이스, DB, 웹개발