[MySQL] CTE 활용하기 - WITH / RECURSIVE WITH

WITH와 RECURSIVE WITH에 대해 공부한 내용 ~

사실 수업에는 WITH 만 나왔지만 검색하다보니 재귀쿼리에 대한 얘기도 같이 나와서 정리해봅니다.

 

1. WITH 

WITH는 일시적인 CTE(Common Table Expression)를 생성해 쿼리의 가독성과 효율성을 높여주는 도구입니다.

쿼리에서 반복되는 서브쿼리를 임시 테이블처럼 정의하고 사용할 수 있는데요? 사용 방법을 보니 간단했습니다.

WITH 절을 먼저 정의하고, 메인 쿼리에서 해당 CTE를 사용하는 방식입니다.

 

Syntax 

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

 

 

2. WITH 사용 예시

Table Schema

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INT
);

Sample Data

INSERT INTO employees (id, name, department, salary) VALUES 
(1, 'Alice', 'Engineering', 100000),
(2, 'Bob', 'Marketing', 85000),
(3, 'Charlie', 'Engineering', 95000),
(4, 'David', 'HR', 60000);

Query using WITH

WITH engineering_team AS (
    SELECT * FROM employees WHERE department = 'Engineering'
)
SELECT * FROM engineering_team;

 

결과는 이러합니다.

 

3. RECURSIVE WITH

Syntax

WITH RECURSIVE cte_name AS (
    -- Anchor member: 재귀가 시작되는 초기 쿼리
    SELECT column1, column2, ... 
    FROM table_name 
    WHERE condition

    UNION ALL

    -- Recursive member: 자신을 다시 참조하는 재귀 쿼리
    SELECT column1, column2, ...
    FROM table_name t
    INNER JOIN cte_name c ON t.parent_column = c.child_column
)
SELECT * FROM cte_name;

 

 

이해하기 위해 알아야 하는 건 이 정도인 것 같습니다 :

• 반드시 UNION 사용해야 함 (보통 UNION ALL을 사용) : 모든 계층을 하나로 결합
• Anchor member : 재귀가 시작되는 초기 쿼리 - 한 번 수행되고, 최상위 데이터를 가져옴
• Recursive member : 자신을 다시 참조하는 재귀 쿼리
• 재귀 종료: 자동으로 데이터가 더 이상 확장되지 않으면 종료

이것 저것 참고하고 이해한 대로 적은건데, 맞는지 확실하게 알 수가 없다는 점이 문제입니다 😔

그렇지만 우선 이해한 대로 적는다.. .

 

계층 예제를 살펴보기 전에 간단하게 숫자로 재귀커리를 작성해보았습니다.

WITH RECURSIVE sumten AS (
	SELECT 1 AS num, 1 AS result
    UNION ALL
    SELECT num + 1 AS num, result + num + 1 AS result FROM sumten
    WHERE num < 10
)
SELECT * FROM sumten;

 

← 굳이 굳이 만들어본 결과는 이렇습니다

 

 

 

 

 

4. RECURSIVE WITH 사용 예제

WITH 와 마찬가지로 테이블을 하나 만들어서 예시를 작성해보겠습니다.

Table Schema

CREATE TABLE organization (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    parent_id INT
);

 

Sample Data

INSERT INTO organization (id, name, department, parent_id) VALUES 
(1, 'Aing', 'Executive', NULL),
(2, 'John', 'Tech', 1),
(3, 'Fulya', 'Service', 1),
(4, 'Talita', 'Business', 1),
(5, 'Bea', 'Tech Strategy', 2),
(6, 'Anita', 'Data Lab', 2),
(7, 'Rachel', 'Infra', 2),
(8, 'Grainne', 'Service Development', 3),
(9, 'Emily', 'Member Platform', 3),
(10, 'Kevin', 'Business Development', 4);

 

순서대로 되어있는데도 그냥 보기만 하면 뭔지 잘 모르겠네요

 

 

계층적 조직도 출력

WITH RECURSIVE org_hierarchy AS (
    SELECT id, name, department, parent_id, 1 AS level
    FROM organization
    WHERE parent_id IS NULL  -- Top-level (CEO)

    UNION ALL

    SELECT o.id, o.name, o.department, o.parent_id, h.level + 1
    FROM organization o
    INNER JOIN org_hierarchy h ON o.parent_id = h.id
)
SELECT * FROM org_hierarchy ORDER BY level;

 

 

이것도 잘 모르겠지만 그냥 조금 더 보기 좋게 만들어 볼게요 ~

WITH RECURSIVE org_hierarchy AS (
    SELECT
		id, name, department, parent_id, 
        1 AS level,
        CAST(id AS CHAR(100)) AS path
    FROM organization 
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT 
        o.id, o.name, o.department, o.parent_id, 
        h.level + 1 AS level,
        CONCAT(h.path, ' > ', o.id) AS path
    FROM organization o
    INNER JOIN org_hierarchy h ON o.parent_id = h.id
)

SELECT 
    CONCAT(REPEAT('     ', level - 1), name) AS name
    department, path, level 
FROM org_hierarchy
ORDER BY path;

 

 

조금 더 나은 느낌? 어쨌든 이렇게 .. 마무리해봅니다...