SQL 서브쿼리 vs CTE — 언제 뭘 써야 할까

처음엔 저도 서브쿼리만 죽어라 썼습니다. 그런데 어느 날 6중 중첩 서브쿼리를 마주하고 나서… 드디어 CTE를 공부하게 됐습니다. 😅

서브쿼리와 CTE — 뭐가 다른 건가요?

쉽게 비유하자면, 서브쿼리는 요리하면서 냄비 안에 냄비를 넣는 것이고, CTE(Common Table Expression)는 재료를 미리 손질해서 이름표를 붙여두는 것입니다. 둘 다 같은 결과를 낼 수 있지만, 코드를 읽는 사람 입장에서는 천지 차이입니다.
서브쿼리는 SELECT 문 안에 또 다른 SELECT를 넣는 방식으로, 간단한 경우엔 편리하지만 중첩이 깊어질수록 읽기가 어려워집니다. 반면 CTE는 WITH 키워드로 임시 결과 집합에 이름을 붙여두고, 마치 테이블처럼 재사용할 수 있습니다.
특히 같은 서브쿼리를 여러 번 반복해서 써야 할 때, CTE를 쓰면 한 번만 정의하고 여러 곳에서 참조할 수 있어서 코드 중복이 확 줄어듭니다. 실무에서 쿼리 유지보수를 해본 분이라면 이게 얼마나 큰 차이인지 바로 공감하실 겁니다.

실전 예제 — 같은 결과, 다른 방식

주문 테이블에서 평균 주문 금액보다 높은 주문을 한 고객 목록을 뽑는 쿼리를 서브쿼리 방식과 CTE 방식으로 각각 작성해 보겠습니다.

-- =============================================
-- 방법 1: 서브쿼리 방식
-- =============================================
SELECT
    c.customer_id,
    c.customer_name,
    o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > (
    -- 전체 주문의 평균 금액을 서브쿼리로 계산
    SELECT AVG(total_amount)
    FROM orders
)
ORDER BY o.total_amount DESC;


-- =============================================
-- 방법 2: CTE 방식 (WITH 절 사용)
-- =============================================
WITH avg_order AS (
    -- 1단계: 평균 주문 금액을 먼저 계산해서 이름 붙이기
    SELECT AVG(total_amount) AS avg_amount
    FROM orders
),
high_value_orders AS (
    -- 2단계: 평균보다 높은 주문만 필터링
    SELECT customer_id, total_amount
    FROM orders
    WHERE total_amount > (SELECT avg_amount FROM avg_order)
)
-- 3단계: 고객 정보와 JOIN해서 최종 결과 출력
SELECT
    c.customer_id,
    c.customer_name,
    h.total_amount
FROM customers c
JOIN high_value_orders h ON c.customer_id = h.customer_id
ORDER BY h.total_amount DESC;


-- =============================================
-- 보너스: CTE를 재사용하는 예제
-- 같은 CTE를 두 번 참조하는 경우
-- =============================================
WITH monthly_sales AS (
    -- 월별 매출 합계를 한 번만 정의
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
        SUM(total_amount)               AS total_sales,
        COUNT(*)                        AS order_count
    FROM orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
-- monthly_sales CTE를 두 번 참조 (서브쿼리였다면 두 번 작성해야 함)
SELECT
    m.sales_month,
    m.total_sales,
    m.order_count,
    -- 전체 평균 대비 이번 달 매출 비율 계산
    ROUND(m.total_sales / avg_m.avg_sales * 100, 1) AS pct_of_avg
FROM monthly_sales m
CROSS JOIN (
    SELECT AVG(total_sales) AS avg_sales FROM monthly_sales
) avg_m
ORDER BY m.sales_month;
  

첫 번째 예제를 보면, 서브쿼리 방식은 WHERE 절 안에 SELECT AVG(...)가 숨어 있어서 한눈에 파악하기 어렵습니다. 반면 CTE 방식은 avg_order라는 이름으로 먼저 정의하고, 이후 단계에서 그 이름을 참조하기 때문에 로직의 흐름이 위에서 아래로 자연스럽게 읽힙니다.
보너스 예제에서 핵심은 CTE 재사용입니다. monthly_sales를 한 번 정의한 뒤, 메인 쿼리와 CROSS JOIN 서브쿼리에서 두 번 참조했습니다. 서브쿼리였다면 같은 집계 로직을 두 번 작성해야 했을 겁니다. 이처럼 같은 결과 집합을 여러 번 써야 할 때 CTE는 압도적으로 유리합니다.
⚠️ 단, MySQL 5.7 이하 버전에서는 CTE를 지원하지 않으니, 레거시 환경에서는 반드시 DB 버전을 확인하세요. MySQL 8.0, PostgreSQL, SQL Server, Oracle 12c 이상에서는 모두 사용 가능합니다.

자주 하는 실수 — 그리고 언제 무엇을 써야 하는가

실무에서 가장 많이 보이는 패턴은 “무조건 서브쿼리” 또는 “무조건 CTE”입니다. 상황에 맞게 골라 쓰는 것이 진짜 실력입니다. 아래 기준을 참고해 보세요.

• 잘못된 방법: 단순한 1회성 필터링에도 CTE를 남발하는 경우입니다. 예를 들어 WHERE id IN (SELECT id FROM ...) 정도의 간단한 조건을 굳이 CTE로 빼면 오히려 코드가 길어지고 가독성이 떨어집니다. CTE는 “이름 붙일 만큼 의미 있는 중간 결과”가 있을 때 써야 합니다.
• 올바른 방법: 서브쿼리는 딱 한 번만 쓰이는 단순한 필터 조건이나 스칼라 값 계산에 적합합니다. CTE는 ① 같은 결과 집합을 2번 이상 참조할 때, ② 쿼리 로직이 여러 단계로 나뉠 때, ③ 재귀 쿼리가 필요할 때 사용하면 가독성과 유지보수성이 크게 올라갑니다.
• 꿀팁: 팀 코드 리뷰에서 “이 쿼리 무슨 의미야?”라는 질문이 나오면 CTE로 리팩터링할 타이밍입니다. CTE 이름을 active_users, monthly_revenue처럼 의미 있게 지으면 주석 없이도 쿼리 의도가 전달됩니다. 또한 재귀 CTE(WITH RECURSIVE)를 활용하면 조직도나 카테고리 트리 같은 계층형 데이터도 깔끔하게 처리할 수 있습니다.

정리하며

서브쿼리와 CTE는 경쟁 관계가 아니라 서로 다른 상황에 맞는 도구입니다. 간단한 조건 필터링엔 서브쿼리가 더 간결하고, 복잡한 다단계 로직이나 중간 결과 재사용이 필요할 땐 CTE가 훨씬 강력합니다.
처음엔 CTE 문법이 낯설게 느껴질 수 있지만, 한 번 손에 익으면 다시는 6중 서브쿼리의 늪으로 돌아가고 싶지 않을 겁니다. 오늘 당장 여러분이 자주 쓰는 복잡한 쿼리 하나를 CTE로 리팩터링해 보세요. 동료들의 코드 리뷰 반응이 달라질 겁니다.
그리고 CTE에 익숙해졌다면, 다음엔 재귀 CTE와 윈도우 함수를 함께 조합하는 방법을 공부해 보시길 추천합니다. 이 두 가지를 같이 쓰면 복잡한 분석 쿼리도 정말 우아하게 작성할 수 있습니다.

• 이 글의 핵심: 서브쿼리는 단순 1회성 조건에, CTE는 재사용·다단계 로직에 사용하면 쿼리 가독성과 유지보수성이 크게 향상됩니다.
• 다음 단계: WITH RECURSIVE 재귀 CTE, 윈도우 함수(ROW_NUMBER, RANK, LAG/LEAD)와 CTE 조합 학습을 추천합니다.

참 쉽죠?