SQL 임의 테이블 생성(WITH, RECURSIVE)

숫자를 0~n 까지만 들어 있는 테이블이 필요 할 때 이를 만들기위하여

(
SELECT 0 as numbers UNION ALL SELECT 1 as numbers ... UNION ALL SELECT n as numbers
)as number_talbe

 

이라는 긴 SELECT 절을 사용 한 후 이를 다시 하나로 묶은 뒤 테이블 명을 지정 해야 하는데

n이 작은 수면 상관이 없으나 작은수가 아닌 큰 수 일때에는 해당 코드는 n 만큼이나 작성해야 한다.

이를 좀 쉽게 사용하기 위해서 WITH RECURSIVE를 사용 하면 간단하게 구현이 가능하다

 

• WITH
  • Common Table Expression(CTE)을 정의하는 데 사용
  • CTE는 복잡한 쿼리를 더 읽기 쉽고 관리하기 쉽게 만드는 데 유용한 임시 결과 집합
  • CTE는 이름을 부여하고, 그 이름을 사용하여 SQL 쿼리에서 참조

WITH EmployeeCTE AS (
    SELECT employee_id, first_name, last_name, department_id
    FROM employees
)
SELECT *
FROM EmployeeCTE;

• WITH RECURSIVE
  • SQL에서 재귀적인 데이터 쿼리를 쉽게 수행할 수 있도록 도와주는 구문
  • 재귀적인 데이터는 계층 구조나 반복적인 패턴을 가지는 데이터로, 예를 들어 직원과 관리자의 관계, 디렉토리 구조, 그래프 탐색 등
  • 기본 구조
    • 기초 쿼리(Anchor Query): 재귀가 시작되는 기본 값들을 선택하는 쿼리.
    • 재귀 쿼리(Recursive Query): 기초 쿼리의 결과를 반복적으로 참조하여 새로운 결과를 생성하는 쿼리.
  • 예제: 0부터 23까지의 숫자 생성

WITH RECURSIVE NumberCTE AS (
    -- 기초 쿼리: 0을 선택
    SELECT 0 AS number
    UNION ALL
    -- 재귀 쿼리: number에 1을 더하여 23까지 선택
    SELECT number + 1
    FROM NumberCTE
    WHERE number < 23
)
-- CTE 결과 선택
SELECT number
FROM NumberCTE;