[DB] 공통 테이블 식(CTE)

SQL 문제를 풀다보면 공통 테이블 식(CTE)을 활용하는 경우가 자주 등장한다.

그렇다면 CTE는 언제, 어떻게 사용해야 할까?

 

---

 

CTE(Common Table Expression)

공통 테이블 식은 쿼리의 결과를 임시적으로 저장해 일종의 테이블처럼 활용할 수 있는 기능이다.

문법은 다음과 같다.

WITH (공통 테이블 이름) AS (
	CTE 쿼리
)
SELECT
	(열 목록)
FROM
	(테이블 이름);

 

WITH 키워드를 통해 공통 테이블 식을 정의하며, AS (...) 안에 CTE 쿼리를 작성해야 한다.

이후에는 SELECT문을 통해 정의한 공통 테이블을 활용할 수 있다.

이때 주의해야 할 점은 다음과 같다.

 

❗️ 하나의 SELECT문에는 하나의 WITH만 사용할 수 있다.
     다시 말해, 한 SELECT 문에 두 개 이상의 WITH를 사용하는 것은 허용되지 않는다.

❓ 만약 SELECT에 공통 테이블 식을 두 개 이상 정의하고 싶다면?

 ⇒ 하나의 WITH 키워드에 공통 테이블 식들을 쉼표(,)로 연달아서 작성한다.

문법은 다음과 같다.

WITH (공통 테이블 이름 1) AS (
    CTE 쿼리 1
), 
(공통 테이블 이름 2) AS (
    CTE 쿼리 2
)
SELECT
	(열 목록)
FROM
	(테이블 이름);

 

---

 

재귀 CTE

재귀 공통 테이블 식은 조건에 따라 자기 자신을 참조하는 공통 테이블 식이다.

자기 참조가 가능한 구조(ex: 계층 구조)를 처리할 때 활용된다.

 

문법은 다음과 같다.

WITH RECURSIVE (공통 테이블 이름) AS (
	-- 1
	SELECT
    	(열 목록)
    FROM
    	(테이블 이름)
    ...
    UNION ALL
    -- 2
    SELECT
    	(열 목록)
    FROM
    	(테이블 이름)
    WHERE
    	(종료 조건)
)
SELECT
	(열 목록)
FROM
	(테이블 이름);

 

다소 구조가 복잡해 보이지만 차근차근 알아보자.

1. 먼저 WITH RECURSIVE 키워드를 붙여, 재귀 공통 테이블 식임을 선언한다.
2. AS 내에
       (1) 1번 쿼리를 통해 재귀를 시작할 초기 행 집합을 정의한다.
       (2) (1)의 행 집합으로부터 종료 조건을 만족할 때까지, 2번 쿼리를 반복적으로 수행하여 새로운 행을 추가한다.

직원의 계층(level)을 포함한 정보들을 출력하기 위한 재귀 공통 테이블 식 쿼리는 다음과 같다. 

WITH RECURSIVE emp_hierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM Employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM Employees e
    JOIN emp_hierarchy h ON e.manager_id = h.id
)
SELECT 
	*
FROM
	emp_hierarchy;

 

UNION ALL 이전 쿼리에서 manager_id가 NULL인 최상위 직원의 기본 정보(id, 이름, 상위 직원의 id)와 계층(level)을 초기 행 집합으로 정의한다.

UNION ALL 이후 쿼리에서는 상위 직원의 아이디(manager_id)가 임시 테이블(emp_hierarchy)에 있으면 하위 직원의 계층(level)을 1 늘린다.

 

---

 

마무리(+ 서브쿼리와의 비교)

지금까지 공통 테이블 식(CTE)에 대해 알아보았다.

공통 테이블 식(CTE)은 복잡한 서브쿼리를 테이블 형식으로 분리하여 가독성을 높이기 위해 사용된다.

뿐만 아니라 서브쿼리로는 재귀를 표현할 수 없으므로, 필요할 경우 공통 테이블 식을 활용하는 것이 좋다!

 

👏

 

참고

https://thinkcatlog.tistory.com/entry/MySQL-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC-Recursive-Common-Table-Expressions

https://luciusfox.tistory.com/155