PostgreSQL(ANSI SQL): CTE와 서브쿼리 비교

※ PostgreSQL: ANSI SQL: Comparison of CTE(Common Table Expression) and Subquery.
 
안녕하세요. 듀스트림입니다. 새해 복 많이 받으세요.
 
2025년의 첫 포스팅은 많이 사용되는 CTE(Common Table Expression)와 서브쿼리(Subquery)의 차이를 살펴보도록 하겠습니다.

---

1. CTE와 서브쿼리의 차이점

항목	CTE	서브쿼리
가독성	직관적	복잡한 구조일수록 어려워짐
재사용성	동일 CTE를 여러 번 참조 가능	재사용 불가
디버깅 용이성	각 CTE 단위로 결과를 쉽게 디버깅 가능	서브쿼리 단위로 디버깅하기는 어려움
성능	반복 참조가 필요한 경우 우수	단순 필터링, 작은 데이터 셋, 중첩 구조가 단순한 경우 우수
코드 구조	명시적으로 분리된 블록 구조	메인 쿼리 내에 포함되어 복잡해질 수 있음
사용 목적	반복적 참조 및 복잡한 쿼리 단순화	단일 작업 수행에 적합

---

2. 특징

CTE	서브쿼리
1. 임시 데이터 집합: 쿼리 실행 중에만 존재 2. 가독성: 복잡한 쿼리를 논리적 단계로 나눔 3. 재귀 지원: 재귀적 데이터를 쉽게 처리 가능 4. 구조화: SQL 코드의 계층적 구조를 지원	1. 중첩 가능: 메인 쿼리의 SELECT, FROM, WHERE 등에서 사용 가능 2. 일회성: 서브쿼리는 한 번 사용 후 재참조 불가 3. 성능: 경우에 따라 직접 최적화 가능 4. 단순 작업: 간단한 조건 필터링이나 계산 작업에 유용

---

3. 장점

CTE	서브쿼리
1. 복잡한 쿼리를 논리적 단계로 나눠 가독성 향상 2. 동일 테이터를 반복 참조 가능 3. 재귀적 데이터 처리에 적합 4. 유지보수 용이(블럭 단위로 코드를 쉽게 수정 가능)	1. 간단한 작업에 적합(코드 작성이 상대적으로 쉬움) 2. 독립적인 데이터 집합으로 제한된 문맥에서 사용 가능 3. 중첩 조건에 따른 필터링이나 계산에 적합

---

4. 주의점

CTE	서브쿼리
1. 성능: CTE는 항상 실행 시 계산되므로 대규모 데이터 셋에서는 성능 저하가 있을 수 있음 2. 임시성: 쿼리 실행 후 데이터 휘발됨	1. 복잡성 증가 : 중첩이 많아지면 코드 가독성이 매우 낮아짐 2. 성능 이슈: 필요 이상으로 중첩 시 성능 저하 3. 재사용성 부족: 동일 데이터를 반복해서 사용할 경우 비효율적

---

5. CTE 문법

▸ 기본 문법

WITH CTE_NAME AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * FROM CTE_NAME;

 
▸ 활용 예시

-- 데이터 정규화(반복 계산 작업 단순화)
WITH Total_Sales AS (
    SELECT salesperson_id, SUM(sales) AS total_sales
    FROM sales_data
    GROUP BY salesperson_id
)
SELECT salesperson_id, total_sales
FROM Total_Sales
WHERE total_sales > 100000;

-- 재귀적 데이터 처리(계층 구조 탐색)
WITH RECURSIVE OrgChart AS (
    SELECT employee_id, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.employee_id, e.manager_id, oc.level + 1
    FROM employees e
    INNER JOIN OrgChart oc
    ON e.manager_id = oc.employee_id
)
SELECT *
FROM OrgChart;

---

6. 서브쿼리 문법

▸ SELECT 절에서 서브쿼리(Scalar Subquery)
→ 스칼라 서브쿼리에 의해 나오는 결과는 '하나의 행'

SELECT employee_id,
       (SELECT department_name
        FROM departments
        WHERE departments.department_id = employees.department_id) AS dept_name
FROM employees;

 
▸ FROM 절에서 서브쿼리(Inline View)
→ 인라인 뷰는 하나의 임시 테이블
→ 메인 쿼리에서는 인라인 뷰에서 SELECT한 컬럼만 사용 가능

SELECT employee_id, department_name
FROM (SELECT employee_id, department_id FROM employees) emp
JOIN departments
ON emp.department_id = departments.department_id;

 
▸ WHERE 절에서 서브쿼리
→ WHERE 절에서 사용하는 서브쿼리 = 서브쿼리

-- 단일행 서브쿼리(서브쿼리의 결과가 1개 행)
SELECT employee_id, first_name
FROM employees
WHERE department_id = (SELECT department_id
                       FROM departments
                       WHERE department_name = 'Sales');
                       
-- 복수행 서브쿼리(서브쿼리의 결과가 여러 행)
SELECT employee_id, first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
                        FROM departments
                        WHERE department_name IN ('Sales', 'Marketing'));

---

7. PostgreSQL에서의 CTE

CTE는 WITH 키워드로 정의되며, 두 가지 방식으로 동작합니다.

1. MATERIALIZED:
   • CTE는 임시 테이블로 처리됩니다.(한 번 계산되고 결과가 저장된 뒤 최종 쿼리에 참조됨)
   • CTE 결과는 쿼리 실행 중 한 번만 계산되고, 이후 재사용될 때 다시 계산되지 않습니다.
   • 반복 참조가 필요한 경우 효율적입니다.
2. NOT MATERIALIZED:
   • CTE는 인라인 처리되어 서브쿼리처럼 동작합니다.(바로 인라인 처리됨)
   • 반복 참조가 없거나, 데이터가 작을 때 유리합니다.(재참조 시 다시 계산됨)
   • PostgreSQL 12 이후로 기본 CTE 동작은 NOT MATERIALIZED로 최적화됩니다.

+ WITH RECURSIVE: 재귀적 데이터 처리를 위해 사용(재귀적 호출은 인라인 처리 방식으로 동작)

---

새해에는 모두 더 행복하셨으면 좋겠습니다-!
그럼 오늘은 여기까지 :)