[PostgreSQL] WITH 절

개요

PostgreSQL DBMS의 WITH 절은 복잡한 쿼리에서 사용할 보조 문을 작성할 수 있는 수단입니다. WITH절은 복잡하고 긴 쿼리를 쉽게 읽을 수 있는 간단한 형태로 분류하는 데 도움을 줍니다. WITH 절을 임시 테이블을 정의하는 것으로 생각할 수 있습니다.

WITH 절은 한 문장에 서브 쿼리가 여러 번 작성될 때 특히 유용합니다. WITH 절을 쿼리에 사용하려면 먼저 정의해야 합니다.

문법

WITH 절 작성법은 다음과 같습니다.

WITH 임시_테이블_명 
AS (
	SELECT Statement
    )
SELECT columns
FROM 임시_테이블_명
WHERE conditions 
ORDER BY columns

With 임시_테이블_명
AS (
       Select
           ID,
           NAME,
           AGE,
           ADDRESS,
           SALARY
       FROM
           COMPANY
   )
Select
    *
From
    임시_테이블_명;

여기서 임시_테이블_명은 WITH 절에 지정된 이름입니다. 

WITH 절에서 데이터 수정문(INSERT, UPDATE 또는 DELETE)을 사용할 수 있습니다. 이렇게 하면 동일한 쿼리에서 여러 가지 작업을 수행할 수 있습니다.

재귀 쿼리

재귀 WITH 절 또는 계층형 WITH 절은 임시 결과집합(테이블)이 자기 자신을 참조할 수 있는 표현방법입니다.

문법

WITH RECURSIVE cte_name AS(
    CTE_query_definition -- non-recursive term
    UNION [ALL]
    CTE_query definion  -- recursive(재귀) term
) SELECT * FROM cte_name;

예제

새 테이블을 만들어 예제를 실습해봅니다.

CREATE TABLE employees (
	employee_id serial PRIMARY KEY,
	full_name VARCHAR NOT NULL,
	manager_id INT
);

직원 테이블에는 세 개의 열(employee_id, manager_id 및 full_name)이 있습니다. manager_id 열은 직원의 관리자 ID를 지정합니다. 다음 문에서는 샘플 데이터를 직원 테이블에 삽입합니다.

INSERT INTO employees (
	employee_id,
	full_name,
	manager_id
)
VALUES
	(1, 'Michael North', NULL),
	(2, 'Megan Berry', 1),
	(3, 'Sarah Berry', 1),
	(4, 'Zoe Black', 1),
	(5, 'Tim James', 1),
	(6, 'Bella Tucker', 2),
	(7, 'Ryan Metcalfe', 2),
	(8, 'Max Mills', 2),
	(9, 'Benjamin Glover', 2),
	(10, 'Carolyn Henderson', 3),
	(11, 'Nicola Kelly', 3),
	(12, 'Alexandra Climo', 3),
	(13, 'Dominic King', 3),
	(14, 'Leonard Gray', 4),
	(15, 'Eric Rampling', 4),
	(16, 'Piers Paige', 7),
	(17, 'Ryan Henderson', 7),
	(18, 'Frank Tucker', 8),
	(19, 'Nathan Ferguson', 8),
	(20, 'Kevin Rampling', 8);

다음 쿼리는 ID가 2인 관리자의 모든 하위 항목을 반환합니다.

WITH RECURSIVE subordinates AS (
	SELECT
		employee_id,
		manager_id,
		full_name
	FROM
		employees
	WHERE
		employee_id = 2
	UNION
		SELECT
			e.employee_id,
			e.manager_id,
			e.full_name
		FROM
			employees e
		INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
	*
FROM
	subordinates;

비재귀 문장은 ID가 2인 직원인 기본 결과 집합을 반환합니다.

 

 employee_id | manager_id |  full_name
-------------+------------+-------------
           2 |          1 | Megan Berry

재귀 문장은 직원 ID 2의 부하 직원 항목을 반환합니다. 직원 테이블과 부하 임시 테이블의 조인 결과입니다. 재귀 항의 첫 번째 반복은 다음 결과 집합을 반환합니다.

 

 employee_id | manager_id |    full_name
-------------+------------+-----------------
           6 |          2 | Bella Tucker
           7 |          2 | Ryan Metcalfe
           8 |          2 | Max Mills
           9 |          2 | Benjamin Glover

PostgreSQL은 재귀 문장을 반복적으로 실행합니다. 재귀 문장의 두 번째 반복은 바로 위의 단계 결과 집합을 입력 값으로 사용하고 다음 결과 집합을 반환합니다.

 employee_id | manager_id |    full_name
-------------+------------+-----------------
          16 |          7 | Piers Paige
          17 |          7 | Ryan Henderson
          18 |          8 | Frank Tucker
          19 |          8 | Nathan Ferguson
          20 |          8 | Kevin Rampling

세 번째  재귀 반복은 ID가 빈 결과 집합을 반환합니다. 부하 직원이 존재하지 않기 때문입니다.

PostgreSQL은 비재귀 및 재귀 문장에 의해 생성된 첫 번째 반복과 두 번째 반복에서 모든 결과 집합의 결합인 최종 결과 집합을 반환합니다.

 employee_id | manager_id |    full_name
-------------+------------+-----------------
           2 |          1 | Megan Berry
           6 |          2 | Bella Tucker
           7 |          2 | Ryan Metcalfe
           8 |          2 | Max Mills
           9 |          2 | Benjamin Glover
          16 |          7 | Piers Paige
          17 |          7 | Ryan Henderson
          18 |          8 | Frank Tucker
          19 |          8 | Nathan Ferguson
          20 |          8 | Kevin Rampling
(10 rows)

 

 

출처: https://tamagotch.tistory.com/101 [다마고치:티스토리]