SQL CTE

SQL 쿼리문을 작성하면서 뭔가 복잡하고 지저분해보여 더 깔끔하고 효율적으로 짤 방법이 없는 지 고민한 적이 많으실 거에요.

저 또한 그런 고민이 많다가 이번에 CTE 라는 기능에 대해 알게 되었습니다!

같이 CTE가 무엇이고 어떻게 사용하는 것인지 알아봅시다.

 

---

1. CTE (Common Table Expression)

CTE란? Common Table Expression의 약자로 말 그대로 공통 테이블 표현식입니다.

복잡한 쿼리를 가독성 좋고, 재사용 가능한 형태로 구조화할 때 유요한 기능이에요.

 

CTE는 쿼리 실행 중에만 일시적으로 존재해요.

DB에 테이블을 저장하는 방식이 아닙니다.

 

또한 한번 선언하면, 하나의 쿼리 내에서 여러번 참조가 가능해요.

복잡한 서브쿼리를 위로 올려서 쿼리를 깔끔하게 구성할 수 있습니다.

 

---

2. CTE 사용방법

그럼 CTE는 어떻게 사용할까요?
CTE는 SELECT, INSERT, UPDATE, DELETE 문 앞에 선언해서, 임시로 이름 붙인 결과셋(=가상의 테이블)을 만드는 구문이에요.

일시적인 뷰(View) 처럼 동작한다고 생각하시면 됩니다.

 

CTE 설정 기본 문법은 다음과 같습니다.

WITH cte_name AS (
    SELECT 컬럼들
    FROM 테이블
    WHERE 조건
)
SELECT *
FROM cte_name
WHERE ...

 

WITH 절을 사용해 WITH cte_name AS (...)으로 CTE를 설정하고 똑같이 테이블처럼 사용하면 됩니다.

이렇게 생성한 CTE 테이블은 해당 쿼리 실행 범위 내에서만 유효하며, 메모리에 일시적으로 존재해요.

 

예시

WITH high_salary AS (
    SELECT emp_id, emp_name, salary
    FROM employees
    WHERE salary > 5000
)
SELECT emp_name
FROM high_salary
WHERE emp_name LIKE 'J%';

위 코드처럼 salay > 5000인 직원들을 high_salary라는 CTE로 묶고, 그 중 이름이 J로 시작하는 사람만 최종적으로 조회합니다.

 

CTE를 여러번 설정할 수 도 있어요!

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY dept_id
),
high_salary AS (
    SELECT emp_id, emp_name, dept_id
    FROM employees
    WHERE salary > 8000
)
SELECT e.emp_name, d.avg_sal
FROM high_salary e
JOIN dept_avg d ON e.dept_id = d.dept_id;

이처럼 dept_avg와 high_salary 라는 두 개의 임시 테이블을 만들어서 서로 조인할 수 있어요.

 

---

3. 재귀(Recursive) CTE

CTE는 재귀 쿼리 또한 가능합니다.

트리 구조를 처리할 때 자주 사용해요.

 

WITH RECURSIVE emp_hierarchy AS (
    -- 1단계: 상위 관리자
    SELECT emp_id, emp_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 2단계 이후: 하위 직원들
    SELECT e.emp_id, e.emp_name, e.manager_id, h.level + 1
    FROM employees e
    JOIN emp_hierarchy h ON e.manager_id = h.emp_id
)
SELECT *
FROM emp_hierarchy;

WITH RECURSIVE를 붙이면 CTE 안에서 자기 자신을 참조할 수 있어요.

이런 방식으로 트리 또는 계층 데이터를 탐색합니다.

 

---

4. 정리하며..

지금까지 SQL의 CTE에 대해 알아보았습니다.

CTE를 활용하면 쿼리의 가독성과 재사용성을 향상 시킬 수 있을 것으로 기대됩니다.

CTE를 사용할 때, 같은 CTE를 여러 번 참조하면, 실제로는 CTE 결과를 재사용하지 않고 매번 다시 읽습니다. 메모리 및 비용을 고려하셔서 CTE를 사용하셔야 할 것 같아요!