이미지 로딩 중...
AI Generated
2025. 11. 23. · 2 Views
CTE와 재귀 쿼리 완벽 가이드
SQL의 CTE(Common Table Expression)와 WITH절을 활용한 복잡한 쿼리 작성법을 배워봅니다. 기본 문법부터 재귀 쿼리까지, 실무에서 바로 활용할 수 있는 계층 구조 데이터 조회 방법을 초급자도 쉽게 이해할 수 있도록 설명합니다.
목차
- CTE(Common_Table_Expression)_개념
- WITH절_기본_문법
- 복잡한_쿼리_가독성_향상
- 재귀_CTE_(WITH_RECURSIVE)
- 계층_구조_데이터_조회
- CTE_vs_서브쿼리_비교
1. CTE(Common_Table_Expression)_개념
시작하며
여러분이 복잡한 SQL 쿼리를 작성할 때, 서브쿼리가 너무 많아서 코드가 뒤죽박죽이 된 경험 있나요? 특히 같은 서브쿼리를 여러 번 반복해서 써야 할 때, 코드가 길어지고 유지보수가 어려워집니다.
이런 문제는 실무에서 정말 자주 발생합니다. 복잡한 집계 쿼리나 여러 단계를 거쳐야 하는 데이터 처리에서 쿼리가 수십 줄이 되고, 나중에 다시 보면 내가 쓴 코드인데도 이해하기 어려워집니다.
팀원들과 협업할 때는 더 큰 문제가 되죠. 바로 이럴 때 필요한 것이 CTE(Common Table Expression)입니다.
CTE는 마치 복잡한 요리를 만들 때 재료를 미리 준비해두는 것처럼, 쿼리의 일부를 먼저 정의하고 이름을 붙여서 나중에 재사용할 수 있게 해줍니다.
개요
간단히 말해서, CTE는 쿼리 안에서 임시로 사용할 수 있는 이름 붙은 결과 집합입니다. 마치 프로그래밍에서 변수를 선언하는 것과 비슷하다고 생각하면 쉽습니다.
왜 이것이 필요할까요? 실무에서 여러분이 매출 데이터를 월별로 집계하고, 그 결과를 다시 분기별로 합산하고, 최종적으로 전년 대비 증가율을 계산해야 하는 상황을 생각해보세요.
서브쿼리만 사용하면 같은 월별 집계 로직을 여러 번 반복해야 하지만, CTE를 사용하면 한 번만 정의하고 계속 재사용할 수 있습니다. 기존에는 복잡한 서브쿼리를 중첩해서 사용하거나, 임시 테이블을 만들어야 했습니다.
하지만 CTE를 사용하면 코드가 위에서 아래로 읽히는 자연스러운 흐름을 가지게 되어, 마치 책을 읽듯이 쿼리를 이해할 수 있습니다. CTE의 핵심 특징은 첫째, 가독성이 뛰어나다는 점입니다.
둘째, 같은 결과 집합을 여러 번 참조할 수 있어 중복 코드를 제거할 수 있습니다. 셋째, 재귀 쿼리를 작성할 수 있어 계층 구조 데이터를 쉽게 다룰 수 있습니다.
이러한 특징들이 실무에서 복잡한 데이터 분석과 보고서 작성을 훨씬 쉽게 만들어줍니다.
코드 예제
-- 월별 매출을 CTE로 정의
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS total_sales
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date)
)
-- CTE를 사용하여 평균보다 높은 월 찾기
SELECT
month,
total_sales,
(SELECT AVG(total_sales) FROM monthly_sales) AS avg_sales
FROM monthly_sales
WHERE total_sales > (SELECT AVG(total_sales) FROM monthly_sales)
ORDER BY month;
설명
이것이 하는 일: 위 쿼리는 2024년도 월별 매출을 계산하고, 평균 매출보다 높은 달만 선택하여 보여줍니다. CTE를 사용하여 월별 매출 계산 로직을 한 번만 정의하고 여러 곳에서 재사용합니다.
첫 번째 단계에서는 WITH 키워드를 사용하여 monthly_sales라는 이름의 CTE를 정의합니다. 이 부분은 마치 레고 블록을 만드는 것과 같습니다.
DATE_TRUNC 함수로 주문 날짜를 월 단위로 자르고, SUM 함수로 각 월의 총 매출을 계산합니다. 이렇게 하면 monthly_sales라는 임시 테이블처럼 사용할 수 있는 결과 집합이 만들어집니다.
두 번째 단계에서는 만들어진 CTE를 일반 테이블처럼 사용합니다. FROM 절에 monthly_sales를 쓰고, WHERE 절에서도 monthly_sales를 참조하여 평균을 계산합니다.
만약 CTE 없이 작성했다면 월별 매출 계산 로직을 최소 3번 반복해야 했을 것입니다. 하지만 CTE 덕분에 한 번만 작성하고 계속 재사용할 수 있습니다.
세 번째 단계에서 최종 결과가 만들어집니다. 각 월의 매출과 전체 평균 매출을 함께 보여주면서, 평균보다 높은 월만 필터링합니다.
이렇게 하면 경영진에게 "어느 달에 매출이 특히 좋았는지" 한눈에 보여줄 수 있는 보고서가 완성됩니다. 여러분이 이 코드를 사용하면 복잡한 집계 쿼리를 훨씬 읽기 쉽게 만들 수 있습니다.
실무에서는 코드의 가독성이 곧 유지보수성이고, 유지보수성이 좋으면 버그를 줄이고 개발 속도를 높일 수 있습니다. 또한 새로운 팀원이 쿼리를 이해하는 시간도 크게 단축됩니다.
실전 팁
💡 CTE 이름은 의미가 명확하게 짓세요. monthly_sales처럼 무엇을 담고 있는지 바로 알 수 있는 이름이 좋습니다. temp1, temp2 같은 이름은 나중에 혼란을 줍니다.
💡 CTE는 쿼리 실행 시마다 계산됩니다. 같은 CTE를 여러 번 참조하면 매번 다시 계산되므로, 성능이 중요하다면 임시 테이블 사용을 고려하세요.
💡 복잡한 쿼리는 여러 개의 CTE로 나누어 작성하세요. 각 CTE가 하나의 명확한 역할을 하도록 설계하면 디버깅이 훨씬 쉬워집니다.
💡 CTE는 해당 쿼리에서만 유효합니다. 다른 쿼리에서 재사용하려면 뷰(VIEW)를 생성하는 것이 좋습니다.
2. WITH절_기본_문법
시작하며
여러분이 CTE를 사용하고 싶은데, 정확한 문법을 몰라서 헤매본 적 있나요? WITH 절을 어디에 써야 하는지, 여러 개의 CTE를 어떻게 연결하는지 막막할 수 있습니다.
SQL 문법은 조금만 틀려도 에러가 나기 때문에 정확히 알고 있어야 합니다. 특히 WITH 절은 일반적인 SELECT 문과는 다른 위치에 작성되기 때문에, 처음 배울 때 많은 분들이 어디에 써야 할지 혼란스러워합니다.
바로 이럴 때 필요한 것이 WITH 절의 기본 문법을 확실히 익히는 것입니다. 한 번 제대로 배워두면 평생 사용할 수 있는 강력한 도구가 됩니다.
개요
간단히 말해서, WITH 절은 항상 메인 쿼리 맨 앞에 작성하며, 하나 이상의 CTE를 정의할 수 있습니다. 각 CTE는 이름과 쿼리로 구성되며, 쉼표로 구분합니다.
왜 이 문법을 정확히 알아야 할까요? 실무에서 여러분이 데이터 분석 보고서를 만들 때, 고객 정보, 주문 정보, 상품 정보를 각각 전처리한 후 조인해야 하는 경우가 많습니다.
이럴 때 여러 개의 CTE를 올바른 순서로 작성하는 방법을 알아야 합니다. 기존에는 서브쿼리를 깊게 중첩하거나, 여러 번의 쿼리 실행으로 임시 테이블을 만들어야 했습니다.
하지만 WITH 절을 사용하면 한 번의 쿼리로 여러 단계의 데이터 처리를 순차적으로 표현할 수 있습니다. WITH 절의 핵심 특징은 첫째, 반드시 쿼리 맨 앞에 위치한다는 점입니다.
둘째, 여러 CTE를 쉼표로 연결할 수 있으며, 뒤에 정의된 CTE가 앞의 CTE를 참조할 수 있습니다. 셋째, 마지막 CTE 뒤에는 반드시 메인 쿼리가 와야 합니다.
이러한 규칙들을 정확히 지키면 복잡한 데이터 처리도 깔끔하게 작성할 수 있습니다.
코드 예제
-- 여러 CTE를 연결하는 기본 문법
WITH
-- 첫 번째 CTE: 활성 고객 추출
active_customers AS (
SELECT customer_id, name, email
FROM customers
WHERE status = 'active'
),
-- 두 번째 CTE: 최근 주문 (첫 번째 CTE 참조)
recent_orders AS (
SELECT o.order_id, o.customer_id, o.amount
FROM orders o
INNER JOIN active_customers ac ON o.customer_id = ac.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
)
-- 메인 쿼리: 고객별 최근 주문 금액 합계
SELECT
ac.name,
ac.email,
COALESCE(SUM(ro.amount), 0) AS total_amount
FROM active_customers ac
LEFT JOIN recent_orders ro ON ac.customer_id = ro.customer_id
GROUP BY ac.customer_id, ac.name, ac.email
ORDER BY total_amount DESC;
설명
이것이 하는 일: 위 쿼리는 활성 고객 중 최근 30일간 주문한 내역을 분석하여, 고객별 총 주문 금액을 계산합니다. 두 개의 CTE를 순차적으로 사용하여 복잡한 비즈니스 로직을 단계별로 명확하게 표현합니다.
첫 번째 단계에서는 WITH 키워드 다음에 첫 번째 CTE인 active_customers를 정의합니다. 이 부분은 전체 고객 테이블에서 활성 상태인 고객만 필터링합니다.
비활성 고객은 마케팅 대상이 아니므로 처음부터 제외하는 것이 효율적입니다. AS 키워드 뒤에 괄호로 감싼 SELECT 쿼리를 작성하면 됩니다.
두 번째 단계에서는 쉼표를 찍고 두 번째 CTE인 recent_orders를 정의합니다. 여기가 중요한데, 이 CTE 안에서 첫 번째 CTE인 active_customers를 일반 테이블처럼 조인할 수 있습니다.
즉, 앞에서 정의한 CTE를 뒤에서 바로 활용하는 것이죠. 이렇게 하면 "활성 고객의 최근 주문"이라는 복잡한 조건을 단계적으로 명확하게 표현할 수 있습니다.
세 번째 단계에서는 메인 쿼리가 실행됩니다. 두 개의 CTE를 모두 사용하여 최종 결과를 만들어냅니다.
active_customers를 기준으로 LEFT JOIN을 하는 이유는, 최근 주문이 없는 활성 고객도 결과에 포함시키기 위해서입니다. COALESCE 함수로 주문이 없는 경우 0으로 표시하여 완전한 고객 리스트를 만듭니다.
마지막으로 GROUP BY로 고객별 집계를 하고 ORDER BY로 주문 금액이 높은 순서대로 정렬합니다. 이렇게 하면 "VIP 고객이 누구인지" 한눈에 파악할 수 있는 마케팅 자료가 완성됩니다.
여러분이 이 문법을 익히면 어떤 복잡한 비즈니스 로직도 단계적으로 쿼리로 표현할 수 있습니다. 실무에서는 이런 패턴이 계속 반복되므로, 한 번 익혀두면 보고서 작성 시간을 절반 이상 단축할 수 있습니다.
또한 쿼리를 리뷰하는 선배 개발자나 DBA가 여러분의 의도를 쉽게 파악할 수 있어 협업이 원활해집니다.
실전 팁
💡 각 CTE 사이에는 쉼표만 있어야 합니다. 세미콜론(;)을 넣으면 에러가 발생하니 주의하세요. 세미콜론은 전체 쿼리의 맨 마지막에만 붙입니다.
💡 CTE 이름 뒤에 컬럼명을 명시할 수도 있습니다. 예: WITH sales(month, amount) AS (...)처럼 작성하면 컬럼명을 명확히 할 수 있습니다.
💡 여러 CTE를 작성할 때는 각 CTE가 독립적인 의미를 가지도록 설계하세요. 하나의 CTE가 너무 많은 일을 하면 가독성이 떨어집니다.
💡 디버깅할 때는 각 CTE를 독립적으로 실행해보세요. 중간 단계의 결과를 확인하면 문제를 빠르게 찾을 수 있습니다.
💡 CTE의 순서가 중요합니다. 뒤의 CTE가 앞의 CTE를 참조할 수 있지만, 앞의 CTE는 뒤의 CTE를 참조할 수 없습니다.
3. 복잡한_쿼리_가독성_향상
시작하며
여러분이 동료가 작성한 100줄짜리 쿼리를 받아서 수정해야 하는데, 서브쿼리가 5단계나 중첩되어 있어서 무슨 일을 하는지 이해하는데 한 시간이 걸린 경험 있나요? 실무에서 이런 상황은 정말 자주 발생합니다.
복잡한 쿼리는 작성한 본인도 일주일만 지나면 이해하기 어렵습니다. 괄호가 어디서 시작해서 어디서 끝나는지 찾느라 시간을 낭비하고, 실수로 잘못된 부분을 수정하여 버그를 만들기도 합니다.
특히 비즈니스 로직이 여러 단계로 나뉘어 있는 경우, 서브쿼리만으로는 그 흐름을 표현하기가 정말 어렵습니다. 바로 이럴 때 필요한 것이 CTE를 활용한 쿼리 리팩토링입니다.
복잡하게 중첩된 서브쿼리를 여러 개의 명확한 CTE로 나누면, 쿼리가 마치 책의 목차처럼 읽히게 됩니다.
개요
간단히 말해서, CTE를 사용하면 복잡한 쿼리를 위에서 아래로 읽히는 단계적인 스토리로 바꿀 수 있습니다. 각 단계가 무슨 일을 하는지 CTE 이름만 봐도 알 수 있게 됩니다.
왜 가독성이 중요할까요? 실무에서 여러분이 작성한 쿼리는 혼자만 보는 것이 아닙니다.
팀원들이 리뷰하고, 나중에 수정하고, 비슷한 기능을 구현할 때 참고합니다. 가독성이 좋은 코드는 버그를 줄이고, 유지보수 시간을 단축하며, 신입 개발자의 학습 속도를 높입니다.
반대로 가독성이 나쁜 코드는 기술 부채가 되어 프로젝트 전체의 발목을 잡습니다. 기존에는 복잡한 쿼리를 이해하기 위해 종이에 그림을 그리거나, 부분적으로 실행해보며 결과를 확인해야 했습니다.
하지만 CTE를 사용하면 쿼리 자체가 자기 설명적(self-documenting)이 되어, 별도의 주석 없이도 의도를 파악할 수 있습니다. CTE를 통한 가독성 향상의 핵심은 첫째, 각 처리 단계에 의미 있는 이름을 붙일 수 있다는 점입니다.
둘째, 논리적 흐름이 위에서 아래로 자연스럽게 이어집니다. 셋째, 각 단계를 독립적으로 테스트하고 수정할 수 있습니다.
이러한 장점들이 대규모 프로젝트에서 쿼리 관리를 훨씬 쉽게 만들어줍니다.
코드 예제
-- 복잡한 서브쿼리를 CTE로 리팩토링
WITH
-- 단계 1: 상품별 월간 판매량 계산
product_monthly_sales AS (
SELECT
product_id,
DATE_TRUNC('month', order_date) AS month,
SUM(quantity) AS total_quantity
FROM order_items
WHERE order_date >= '2024-01-01'
GROUP BY product_id, DATE_TRUNC('month', order_date)
),
-- 단계 2: 각 상품의 평균 월간 판매량
product_avg_sales AS (
SELECT
product_id,
AVG(total_quantity) AS avg_monthly_quantity
FROM product_monthly_sales
GROUP BY product_id
),
-- 단계 3: 평균보다 높은 달 카운트
high_performance_months AS (
SELECT
pms.product_id,
COUNT(*) AS months_above_average
FROM product_monthly_sales pms
INNER JOIN product_avg_sales pas ON pms.product_id = pas.product_id
WHERE pms.total_quantity > pas.avg_monthly_quantity
GROUP BY pms.product_id
)
-- 최종: 인기 상품 목록 (평균 이상 판매 달이 6개월 이상)
SELECT
p.product_name,
pas.avg_monthly_quantity,
hpm.months_above_average
FROM high_performance_months hpm
INNER JOIN products p ON hpm.product_id = p.product_id
INNER JOIN product_avg_sales pas ON hpm.product_id = pas.product_id
WHERE hpm.months_above_average >= 6
ORDER BY hpm.months_above_average DESC, pas.avg_monthly_quantity DESC;
설명
이것이 하는 일: 위 쿼리는 2024년도 상품별 판매 데이터를 분석하여, 꾸준히 평균 이상의 성과를 내는 인기 상품을 찾아냅니다. 4단계의 CTE를 통해 복잡한 집계와 비교 로직을 명확하게 표현합니다.
첫 번째 단계에서는 product_monthly_sales라는 CTE로 기초 데이터를 준비합니다. 각 상품이 각 월에 얼마나 팔렸는지 집계하는 것이죠.
이 단계는 마치 요리할 때 재료를 씻고 다듬는 과정과 같습니다. DATE_TRUNC 함수로 날짜를 월 단위로 묶고, SUM으로 수량을 합산합니다.
만약 이것을 서브쿼리로 작성했다면, 아래 단계에서 이 로직을 여러 번 반복해야 했을 것입니다. 두 번째 단계에서는 product_avg_sales로 각 상품의 평균 월간 판매량을 계산합니다.
첫 번째 CTE의 결과를 입력으로 받아 한 단계 더 가공하는 것이죠. 이렇게 단계를 나누면 "왜 평균을 계산하는가?"라는 의도가 명확해집니다.
CTE 이름만 봐도 "아, 평균 판매량을 구하는구나"라고 바로 이해할 수 있습니다. 세 번째 단계에서는 high_performance_months로 각 상품이 평균 이상 판매된 달이 몇 개월인지 셉니다.
첫 번째와 두 번째 CTE를 조인하여 실제 판매량과 평균을 비교하는 것이죠. 이 부분이 만약 서브쿼리로 중첩되어 있었다면, 괄호 안에 괄호가 여러 겹 들어가서 읽기가 정말 어려웠을 것입니다.
하지만 CTE로 나누니 각 단계의 역할이 명확합니다. 마지막 메인 쿼리에서는 세 개의 CTE를 모두 활용하여 최종 결과를 만듭니다.
6개월 이상 평균 이상의 성과를 낸 상품만 필터링하고, 상품명과 통계를 함께 보여줍니다. 이렇게 하면 경영진에게 "어떤 상품에 마케팅 예산을 집중해야 하는가"에 대한 명확한 인사이트를 제공할 수 있습니다.
여러분이 이런 방식으로 쿼리를 작성하면, 6개월 후에 다시 봐도 5분 안에 이해할 수 있습니다. 실무에서는 같은 쿼리를 여러 번 수정하는 경우가 많기 때문에, 초기에 조금 더 시간을 들여 가독성 좋게 작성하면 나중에 몇 배의 시간을 절약할 수 있습니다.
또한 코드 리뷰에서 동료들이 로직을 빠르게 이해하여 더 좋은 피드백을 줄 수 있습니다.
실전 팁
💡 각 CTE의 이름은 동사로 시작하거나 명사형으로 만드세요. "무엇을 하는가" 또는 "무엇을 담고 있는가"가 명확해야 합니다.
💡 한 CTE가 100줄이 넘어간다면 더 작은 CTE로 분리할 수 있는지 고민하세요. 한 화면에 들어오는 크기가 이상적입니다.
💡 CTE 사이에 주석을 달아 각 단계의 목적을 설명하면 더 좋습니다. "-- 단계 1: 기초 데이터 집계" 같은 식으로요.
💡 복잡한 기존 쿼리를 리팩토링할 때는 결과가 동일한지 반드시 확인하세요. 양쪽 쿼리를 실행하여 EXCEPT 연산으로 차이를 검증하는 것이 좋습니다.
💡 성능이 걱정된다면 실행 계획(EXPLAIN ANALYZE)을 확인하세요. 대부분의 경우 CTE와 서브쿼리의 성능은 비슷하지만, 데이터베이스마다 최적화 방식이 다를 수 있습니다.
4. 재귀_CTE_(WITH_RECURSIVE)
시작하며
여러분이 조직도를 데이터베이스에 저장하고, "특정 직원의 모든 부하 직원을 찾아라"는 요구사항을 받았을 때 어떻게 하시겠어요? 부서가 몇 단계인지 모르는 상황에서, 일반 SQL로는 해결하기가 정말 어렵습니다.
이런 계층 구조 문제는 실무에서 엄청나게 자주 나타납니다. 조직도, 카테고리 트리, 댓글의 대댓글, 파일 시스템, BOM(Bill of Materials) 등 수많은 도메인에서 부모-자식 관계가 반복되는 구조를 다루게 됩니다.
일반 쿼리로는 깊이를 미리 알아야 조인 횟수를 결정할 수 있는데, 실제로는 깊이가 가변적인 경우가 대부분이죠. 바로 이럴 때 필요한 것이 재귀 CTE(WITH RECURSIVE)입니다.
재귀 CTE는 자기 자신을 참조하면서 반복적으로 데이터를 탐색할 수 있는 강력한 기능입니다.
개요
간단히 말해서, 재귀 CTE는 자기 자신의 결과를 다시 입력으로 사용하여 반복 작업을 수행하는 CTE입니다. 마치 러시아 인형처럼 자신 안에 자신이 들어있는 구조라고 생각하면 됩니다.
왜 재귀가 필요할까요? 실무에서 여러분이 전자상품 카테고리를 관리한다고 해봅시다.
"전자제품 > 컴퓨터 > 노트북 > 게이밍 노트북"처럼 카테고리가 몇 단계인지 미리 알 수 없습니다. 신제품 카테고리가 추가될 때마다 깊이가 달라질 수 있죠.
재귀 CTE를 사용하면 깊이에 상관없이 모든 하위 카테고리를 한 번의 쿼리로 가져올 수 있습니다. 기존에는 애플리케이션 레벨에서 반복문을 돌리며 여러 번 쿼리를 실행하거나, 저장 프로시저에 복잡한 로직을 작성해야 했습니다.
하지만 재귀 CTE를 사용하면 데이터베이스 엔진이 효율적으로 계층 구조를 탐색하여, 네트워크 왕복 없이 한 번에 결과를 가져올 수 있습니다. 재귀 CTE의 핵심은 두 부분으로 구성된다는 점입니다.
첫째는 앵커 멤버(anchor member)로, 재귀의 시작점이 되는 초기 데이터를 정의합니다. 둘째는 재귀 멤버(recursive member)로, 이전 단계의 결과를 참조하여 다음 단계를 계산합니다.
이 두 부분을 UNION ALL로 연결하면, 더 이상 결과가 나오지 않을 때까지 자동으로 반복됩니다.
코드 예제
-- 조직도에서 특정 직원의 모든 부하 직원 찾기
WITH RECURSIVE employee_hierarchy AS (
-- 앵커 멤버: 시작점 (CEO 또는 특정 관리자)
SELECT
employee_id,
name,
manager_id,
1 AS level,
name AS path
FROM employees
WHERE employee_id = 100 -- 시작 직원 ID
UNION ALL
-- 재귀 멤버: 이전 결과의 부하 직원 찾기
SELECT
e.employee_id,
e.name,
e.manager_id,
eh.level + 1,
eh.path || ' > ' || e.name
FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
WHERE eh.level < 10 -- 무한 루프 방지 (최대 깊이)
)
SELECT
employee_id,
name,
level,
path AS organizational_path
FROM employee_hierarchy
ORDER BY level, name;
설명
이것이 하는 일: 위 쿼리는 직원 ID 100번인 관리자의 모든 부하 직원을 계층적으로 찾아냅니다. 직속 부하뿐만 아니라 부하의 부하, 그 아래 모든 레벨까지 자동으로 탐색합니다.
첫 번째 단계인 앵커 멤버에서는 재귀의 출발점을 정의합니다. employee_id가 100인 직원을 찾아서 level을 1로 설정하고, path에는 자기 이름만 넣습니다.
이 부분은 마치 나무의 뿌리를 심는 것과 같습니다. 여기서 시작하여 점점 아래로 가지가 뻗어나갈 것입니다.
두 번째 단계인 재귀 멤버에서 마법이 일어납니다. employee_hierarchy(자기 자신)를 조인하여, 이전 단계에서 찾은 직원들의 부하 직원을 찾습니다.
핵심은 e.manager_id = eh.employee_id 조건입니다. 이것은 "현재 직원의 상사가 이전 단계에서 찾은 직원인가?"를 확인하는 것이죠.
level은 1씩 증가시키고, path는 이전 경로에 현재 직원 이름을 붙여서 전체 조직 경로를 만듭니다. 세 번째 단계에서 UNION ALL이 두 부분을 연결합니다.
데이터베이스는 먼저 앵커 멤버를 실행하여 초기 결과를 얻고, 그 결과를 재귀 멤버의 입력으로 사용합니다. 재귀 멤버가 새로운 행을 반환하면, 그 결과가 다시 재귀 멤버의 입력이 됩니다.
이 과정이 더 이상 새로운 행이 나오지 않을 때까지 반복됩니다. 네 번째 단계에서는 WHERE eh.level < 10 조건으로 무한 루프를 방지합니다.
만약 데이터에 실수로 순환 참조가 있다면(A의 상사가 B이고 B의 상사가 A인 경우), 쿼리가 영원히 실행될 수 있습니다. 최대 깊이를 설정하면 이런 상황을 예방할 수 있습니다.
마지막 메인 쿼리에서는 재귀 CTE의 모든 결과를 level 순서로 정렬하여 보여줍니다. path 컬럼을 보면 "김대표 > 이부장 > 박차장 > 최대리"처럼 전체 보고 라인을 한눈에 볼 수 있습니다.
여러분이 이 기법을 익히면 계층 구조 문제를 만났을 때 더 이상 두려워하지 않게 됩니다. 실무에서 조직도 조회, 카테고리 트리 생성, 댓글 스레드 표시 등 수많은 기능을 우아하게 구현할 수 있습니다.
특히 애플리케이션 코드에서 복잡한 반복문을 작성하지 않아도 되어, 코드가 훨씬 간결해지고 버그가 줄어듭니다.
실전 팁
💡 반드시 종료 조건을 설정하세요. level 제한이나 특정 조건을 넣어 무한 루프를 방지해야 합니다.
💡 PostgreSQL과 MySQL은 WITH RECURSIVE를 지원하지만, Oracle은 CONNECT BY 구문을 사용합니다. 데이터베이스마다 문법이 다르니 확인하세요.
💡 대량의 계층 데이터를 다룰 때는 인덱스가 중요합니다. manager_id 컬럼에 인덱스를 생성하면 성능이 크게 향상됩니다.
💡 재귀 깊이가 매우 깊다면(100단계 이상) 성능 문제가 발생할 수 있습니다. 이런 경우 Nested Set이나 Materialized Path 같은 다른 계층 구조 모델을 고려하세요.
💡 디버깅할 때는 level을 출력하여 각 단계에서 얼마나 많은 행이 나오는지 확인하세요. 예상보다 많은 행이 나온다면 조인 조건을 재검토해야 합니다.
5. 계층_구조_데이터_조회
시작하며
여러분이 전자상거래 사이트에서 "전자제품" 카테고리를 클릭했을 때, 그 아래의 모든 하위 카테고리 상품을 보여줘야 하는 상황을 생각해보세요. "전자제품 > 컴퓨터" 뿐만 아니라 "전자제품 > 컴퓨터 > 노트북 > 게이밍 노트북"의 상품도 함께 보여야 합니다.
이런 계층 구조 조회는 실무에서 정말 자주 나오는 요구사항입니다. 게시판의 댓글과 대댓글, 파일 탐색기의 폴더 구조, 회사의 부서 조직도 등 수많은 곳에서 사용됩니다.
문제는 깊이가 가변적이라는 점입니다. 어떤 카테고리는 2단계, 어떤 카테고리는 5단계일 수 있죠.
바로 이럴 때 필요한 것이 재귀 CTE를 활용한 계층 구조 조회입니다. 한 번의 쿼리로 모든 하위 항목을 가져와서 효율적으로 처리할 수 있습니다.
개요
간단히 말해서, 계층 구조 데이터 조회는 재귀 CTE를 사용하여 부모-자식 관계로 연결된 모든 데이터를 탐색하는 것입니다. 트리 구조를 따라 내려가며 모든 노드를 방문하는 것이죠.
왜 이것이 실무에서 중요할까요? 사용자가 카테고리를 클릭했을 때, 백엔드에서 SQL 쿼리를 여러 번 실행하면 응답 시간이 느려집니다.
네트워크 왕복이 반복되고, 애플리케이션 서버에서 복잡한 로직을 처리해야 하죠. 하지만 재귀 CTE를 사용하면 데이터베이스가 한 번에 모든 계층을 탐색하여, 응답 속도가 빨라지고 서버 부하가 줄어듭니다.
기존에는 애플리케이션에서 첫 번째 쿼리로 직속 자식을 가져오고, 반복문을 돌며 각 자식의 또 다른 자식을 가져오는 방식이었습니다. N+1 쿼리 문제가 발생하고, 코드가 복잡해지며, 성능도 좋지 않았죠.
재귀 CTE를 사용하면 이 모든 문제가 해결됩니다. 계층 구조 조회의 핵심은 첫째, 시작점(루트 노드)을 명확히 정의하는 것입니다.
둘째, 부모-자식 관계를 따라 재귀적으로 탐색합니다. 셋째, 결과에 깊이(level)나 경로(path) 정보를 포함시켜 나중에 활용할 수 있게 합니다.
넷째, 정렬 순서를 고려하여 사용자에게 의미 있는 순서로 데이터를 제공합니다.
코드 예제
-- 특정 카테고리의 모든 하위 카테고리와 상품 조회
WITH RECURSIVE category_tree AS (
-- 앵커: 시작 카테고리 (예: 전자제품)
SELECT
category_id,
parent_id,
name,
1 AS level,
CAST(name AS VARCHAR(1000)) AS path,
CAST(category_id AS VARCHAR(1000)) AS id_path
FROM categories
WHERE category_id = 1 -- 전자제품 카테고리 ID
UNION ALL
-- 재귀: 하위 카테고리 찾기
SELECT
c.category_id,
c.parent_id,
c.name,
ct.level + 1,
ct.path || ' > ' || c.name,
ct.id_path || ',' || CAST(c.category_id AS VARCHAR)
FROM categories c
INNER JOIN category_tree ct ON c.parent_id = ct.category_id
)
-- 모든 하위 카테고리의 상품 조회
SELECT
ct.level,
ct.path AS category_path,
p.product_id,
p.product_name,
p.price
FROM category_tree ct
LEFT JOIN products p ON p.category_id = ct.category_id
ORDER BY ct.id_path, p.product_name;
설명
이것이 하는 일: 위 쿼리는 "전자제품" 카테고리(ID=1)의 모든 하위 카테고리를 재귀적으로 찾고, 각 카테고리에 속한 상품을 함께 가져옵니다. 사용자가 카테고리 페이지를 열었을 때 필요한 모든 정보를 한 번에 제공합니다.
첫 번째 단계인 앵커 멤버에서는 시작 카테고리를 선택합니다. category_id = 1인 "전자제품"을 찾아서 level을 1로 설정하고, path에는 카테고리 이름을 넣습니다.
특별히 id_path라는 컬럼도 만드는데, 이것은 나중에 정렬할 때 유용합니다. CAST를 사용하는 이유는 재귀 과정에서 문자열이 계속 길어지기 때문에 충분한 크기를 확보하기 위함입니다.
두 번째 단계인 재귀 멤버에서는 이전 단계에서 찾은 카테고리들의 자식 카테고리를 찾습니다. c.parent_id = ct.category_id 조건이 핵심인데, 이것은 "현재 카테고리의 부모가 이전 단계에서 찾은 카테고리인가?"를 확인합니다.
level은 부모보다 1 증가시키고, path는 부모 경로에 현재 이름을 붙입니다. id_path도 부모 경로에 현재 ID를 추가하여 계속 누적됩니다.
세 번째 단계에서 재귀가 반복됩니다. 2단계 카테고리를 찾았으면, 그 결과를 입력으로 3단계 카테고리를 찾고, 계속해서 더 이상 자식이 없을 때까지 탐색합니다.
예를 들어 "전자제품 > 컴퓨터 > 노트북 > 게이밍 노트북 > 고성능 게이밍 노트북"처럼 5단계까지 자동으로 탐색됩니다. 네 번째 단계인 메인 쿼리에서는 category_tree의 결과를 products 테이블과 LEFT JOIN합니다.
LEFT JOIN을 사용하는 이유는 상품이 없는 카테고리도 결과에 포함시키기 위해서입니다. 빈 카테고리도 사용자에게 보여주어야 할 때가 있기 때문이죠.
마지막으로 ORDER BY ct.id_path를 사용하여 정렬합니다. id_path는 "1,3,7"처럼 조상부터 자손까지의 ID 경로이므로, 이것으로 정렬하면 트리 구조가 유지된 상태로 결과가 나옵니다.
같은 카테고리 내에서는 product_name으로 2차 정렬하여 상품을 가나다순으로 보여줍니다. 여러분이 이 패턴을 익히면 전자상거래 사이트의 카테고리 네비게이션, 게시판의 댓글 트리, 파일 탐색기 같은 기능을 쉽게 구현할 수 있습니다.
실무에서 "이 부서의 모든 하위 부서 직원 수 합계", "이 폴더와 하위 폴더의 총 파일 크기" 같은 복잡한 요구사항도 우아하게 해결됩니다.
실전 팁
💡 id_path를 저장하면 정렬과 경로 추적이 쉬워집니다. 특히 breadcrumb 네비게이션을 만들 때 유용합니다.
💡 대량의 계층 데이터를 자주 조회한다면, Materialized Path나 Nested Set 모델을 고려하세요. 조회 성능은 더 빠르지만 수정이 복잡해집니다.
💡 LEFT JOIN을 사용할지 INNER JOIN을 사용할지 비즈니스 요구사항을 명확히 하세요. 빈 카테고리를 보여줄 것인지 숨길 것인지에 따라 다릅니다.
💡 카테고리 트리가 변경되는 빈도가 낮다면 캐싱을 고려하세요. 애플리케이션 레벨에서 결과를 캐싱하면 데이터베이스 부하를 크게 줄일 수 있습니다.
💡 사용자 인터페이스에서 트리를 펼치고 접을 수 있게 만들려면, level 정보를 프론트엔드에 전달하여 들여쓰기에 활용하세요.
6. CTE_vs_서브쿼리_비교
시작하며
여러분이 복잡한 쿼리를 작성할 때, "CTE를 쓸까? 서브쿼리를 쓸까?" 고민해본 적 있나요?
두 가지 모두 비슷한 결과를 만들 수 있는데, 언제 무엇을 사용해야 할지 헷갈립니다. 실무에서 이 선택은 단순히 개인 취향의 문제가 아닙니다.
코드 가독성, 유지보수성, 성능, 팀의 코딩 스타일 등 여러 요소를 고려해야 합니다. 잘못된 선택은 나중에 큰 기술 부채가 될 수 있죠.
특히 복잡한 프로젝트에서는 일관된 쿼리 작성 패턴이 중요합니다. 바로 이럴 때 필요한 것이 CTE와 서브쿼리의 차이점과 각각의 장단점을 정확히 이해하는 것입니다.
상황에 맞는 최선의 선택을 할 수 있어야 합니다.
개요
간단히 말해서, CTE는 가독성과 재사용성에 강점이 있고, 서브쿼리는 간단한 경우 더 직관적일 수 있습니다. 각각 적합한 사용 사례가 다릅니다.
왜 이 차이를 알아야 할까요? 실무에서 여러분이 간단한 필터링 조건 하나를 추가하는데 CTE를 사용하면 과도한 엔지니어링이 됩니다.
반대로 복잡한 다단계 집계를 서브쿼리로만 작성하면 코드가 읽기 어려워져서 버그의 온상이 됩니다. 적재적소에 맞는 도구를 선택하는 것이 전문 개발자의 자질입니다.
기존에는 많은 개발자들이 서브쿼리에만 익숙해서 모든 상황에 서브쿼리를 사용했습니다. 하지만 CTE를 배우고 나면, 복잡한 쿼리를 작성하는 새로운 관점을 얻게 됩니다.
두 가지 도구를 모두 자유롭게 사용할 수 있을 때 진정한 SQL 마스터가 됩니다. CTE와 서브쿼리의 핵심 차이는 첫째, CTE는 이름을 가지고 여러 번 참조할 수 있지만 서브쿼리는 재사용이 어렵습니다.
둘째, CTE는 재귀를 지원하지만 서브쿼리는 불가능합니다. 셋째, CTE는 위에서 아래로 읽히는 순차적 구조이지만, 서브쿼리는 안에서 밖으로 읽어야 합니다.
넷째, 성능 면에서는 데이터베이스마다 최적화 방식이 다르므로 실제 테스트가 필요합니다.
코드 예제
-- 서브쿼리 방식: 중첩되고 재사용 어려움
SELECT
c.customer_name,
o.order_count,
o.total_amount
FROM customers c
INNER JOIN (
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY customer_id
) o ON c.customer_id = o.customer_id
WHERE o.total_amount > (
SELECT AVG(sub.total_amount)
FROM (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY customer_id
) sub
);
-- CTE 방식: 명확하고 재사용 가능
WITH recent_orders AS (
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY customer_id
),
avg_amount AS (
SELECT AVG(total_amount) AS avg_total
FROM recent_orders
)
SELECT
c.customer_name,
ro.order_count,
ro.total_amount
FROM customers c
INNER JOIN recent_orders ro ON c.customer_id = ro.customer_id
CROSS JOIN avg_amount aa
WHERE ro.total_amount > aa.avg_total;
설명
이것이 하는 일: 두 쿼리 모두 최근 90일간 평균 이상의 주문 금액을 기록한 고객을 찾습니다. 같은 결과를 만들지만 접근 방식이 완전히 다릅니다.
첫 번째 서브쿼리 방식을 보면, FROM 절 안에 서브쿼리가 있고, WHERE 절 안에 또 다른 서브쿼리가 있습니다. 더 심각한 것은 두 서브쿼리가 거의 같은 로직(최근 90일 주문 집계)을 반복한다는 점입니다.
이것은 코드 중복이며, 나중에 기간을 바꾸려면 두 곳을 모두 수정해야 합니다. 하나만 수정하면 버그가 발생하죠.
또한 괄호가 여러 겹 중첩되어 있어서, 어느 괄호가 어디서 시작하고 끝나는지 찾기 어렵습니다. 두 번째 CTE 방식을 보면, 먼저 recent_orders로 90일 주문 데이터를 한 번만 정의합니다.
그 다음 avg_amount로 평균을 계산하는데, 이때 recent_orders를 재사용합니다. 같은 로직을 두 번 쓸 필요가 없죠.
메인 쿼리는 두 개의 CTE를 조합하여 최종 결과를 만듭니다. 각 단계가 무슨 일을 하는지 이름만 봐도 알 수 있고, 위에서 아래로 읽으면 로직이 자연스럽게 이해됩니다.
성능 측면에서 보면, 많은 경우 데이터베이스 옵티마이저가 두 방식을 비슷하게 최적화합니다. 하지만 CTE의 경우 일부 데이터베이스(예: PostgreSQL의 이전 버전)는 CTE를 항상 구체화(materialize)하여, 같은 CTE를 여러 번 참조해도 한 번만 실행합니다.
반면 서브쿼리는 참조할 때마다 재실행될 수 있습니다. 최신 버전에서는 이런 차이가 많이 줄어들었지만, 실제 프로젝트에서는 EXPLAIN ANALYZE로 확인하는 것이 안전합니다.
유지보수 측면에서는 CTE가 압도적으로 유리합니다. 6개월 후 쿼리를 수정해야 할 때, CTE 버전은 5분이면 이해할 수 있지만 서브쿼리 버전은 30분이 걸릴 수 있습니다.
특히 팀에 신입 개발자가 들어왔을 때, CTE로 작성된 코드베이스는 학습 속도를 크게 높여줍니다. 그렇다면 언제 서브쿼리를 사용해야 할까요?
정말 간단한 경우입니다. 예를 들어 WHERE price > (SELECT AVG(price) FROM products) 같이 한 줄로 끝나는 경우는 서브쿼리가 더 직관적입니다.
CTE를 만들면 오히려 과도한 엔지니어링이 됩니다. 기준은 "재사용하는가?", "로직이 복잡한가?", "재귀가 필요한가?"입니다.
하나라도 예라면 CTE를 사용하세요. 여러분이 이 차이를 이해하면 상황에 맞는 최적의 선택을 할 수 있습니다.
실무에서는 간단한 쿼리는 서브쿼리로, 복잡한 쿼리는 CTE로 작성하는 것이 일반적인 베스트 프랙티스입니다. 팀의 코딩 컨벤션을 정할 때 이런 기준을 명확히 해두면 일관성 있는 코드베이스를 유지할 수 있습니다.
실전 팁
💡 3개 이상의 테이블을 조인하거나 같은 집계를 여러 곳에서 사용한다면 무조건 CTE를 사용하세요. 가독성과 유지보수성이 크게 향상됩니다.
💡 성능이 중요한 쿼리는 양쪽 방식으로 작성하여 실행 계획을 비교하세요. 데이터베이스 버전과 데이터 특성에 따라 결과가 다를 수 있습니다.
💡 일부 데이터베이스(SQL Server 등)는 CTE에 인덱스 힌트를 줄 수 있습니다. 이런 고급 기능이 필요하면 해당 DB의 문서를 참고하세요.
💡 스칼라 서브쿼리(SELECT 절의 서브쿼리)는 성능 문제를 일으킬 수 있습니다. 대부분 조인이나 CTE로 재작성하는 것이 좋습니다.
💡 팀 컨벤션을 정할 때는 "2단계 이상 중첩되면 CTE 사용" 같은 명확한 규칙을 만드세요. 모두가 같은 스타일로 작성하면 코드 리뷰가 쉬워집니다.
댓글 (0)
함께 보면 좋은 카드 뉴스
SQL 실전 종합 프로젝트 완벽 가이드
전자상거래 시스템을 직접 구축하면서 배우는 SQL 실전 프로젝트입니다. DB 설계부터 성능 최적화까지, 실무에서 필요한 모든 SQL 기술을 단계별로 마스터할 수 있습니다. 초급 개발자도 따라하기 쉬운 친절한 가이드로 구성되어 있습니다.
실무 데이터 분석 SQL 완벽 가이드
실제 업무에서 자주 사용하는 SQL 데이터 분석 기법을 단계별로 학습합니다. 매출 집계부터 고객 세분화까지, 실전 대시보드 쿼리 작성 방법을 배워보세요.
데이터 모델링과 정규화 완벽 가이드
데이터베이스 설계의 핵심인 데이터 모델링과 정규화를 초급 개발자 눈높이에서 쉽게 설명합니다. ERD 작성부터 제1~3정규형, 정규화의 장단점, 비정규화 전략, 실무 설계 패턴까지 실전에서 바로 활용할 수 있는 노하우를 담았습니다.
트랜잭션과 ACID 원칙 완벽 가이드
데이터베이스의 핵심 개념인 트랜잭션과 ACID 원칙을 초급 개발자도 쉽게 이해할 수 있도록 실무 예제와 함께 설명합니다. 안전한 데이터 처리를 위한 필수 지식을 친근하게 배워보세요.
인덱스와 쿼리 성능 최적화 완벽 가이드
데이터베이스 성능의 핵심인 인덱스를 처음부터 끝까지 배워봅니다. B-Tree 구조부터 실행 계획 분석까지, 실무에서 바로 사용할 수 있는 인덱스 최적화 전략을 초급자도 이해할 수 있게 설명합니다.