SQL의 재귀 쿼리는 엄마 함수를 본다

예제 1: 초기 구문 한 개

먼저 이 구문으로 시작하겠다.

SELECT *
FROM my_table
WHERE target_column LIKE CHR(65) || '%';

이 기저 구문 자체를 이해하지 못하는 프로그래머를 찾는 것이 더 난관일 것이다. 기본적으로 이것은 아래와 같다.

SELECT *
FROM my_table
WHERE target_column LIKE 'A%';

둘이 같은 까닭

ASCII 코드표에 따라 다음과 같은 관계가 있다.

CHR(값)	변환값
CHR(65)	A
CHR(66)	B
CHR(67)	C
......	..
CHR(90)	Z

즉 Z 변환값까지 쭈욱 x += 1을 수행한다.

그렇다면 문자열 잇기 연산자를 아직 보존한 채 다시 써 보자.

CHR(65) || '%' 는 'A' || '%'이다.

실제로 문자열을 이으면 아래와 같다.

'A%'

예제 2: 재귀 호출로 Z까지 전진

예제 2-1: 일반적인 프로그래밍 언어에서

A=65를 기저로 둔 채 Z까지 전진하기 위해서 보통 프로그래머들은 아래와 같이 생각할 것이다.

for(char c = 'A'; c <= 'Z'; c++);

파이썬이라도 같다.

def func1(c):
    while c <= 90:
        print(chr(c), end='')
        c += 1

def main():
    func1(65)

main()

예제 2-2: SQL 계열에서

안타깝게도 SQL에서 반복문은 구현체마다 다르거나, 없기도 하는 등 이렇다 할 통일된 규격이 없다.

이 경우 재귀 호출을 쓰게 될 것이다. C언어나 파이썬으로 따지면 이렇다.

void call_alphabet(char c) {
    if(c == 'Z') return;
    else call_alphabet(++c);
}

void main() {
    call_alphabet('A');
}

def call_alphabet(c):
  print(chr(c), end='')
  if c == 90:
      return
  c += 1
  call_alphabet(c)

def main():
  call_alphabet(65)

main()

예제 2-3: 자모도(子母圖)로 보는 재귀

그러나, 만약 문자열 값 비교를 중간에 사용할 수 없으며, 재귀 깊이만 제한할 수 있다면 어떨까?

이것을 한국의 수학 교재들에서 많이 나오는 엄마, 자식 그림으로 이해해 보자.

엄마 함수	자식 함수
func(65)	func(65+1) = func(66)
func(66)	func(66+1) = func(67)
.....	......
func(89)	func(89+1) = func(90)

65대손을 기점으로 하여 89대손이 마지막으로 자녀를 낳고 한 방계 가문이 끊긴다고 해 보자.

그럼 89대손이 낳은 자식은 90대손이 되고, 이후로 자녀가 없다.

SQL은 자식 함수의 종료에 초점을 맞추기보다 엄마 함수의 마지막 점화 시점에 초점을 두는 것을 일반적인 관행으로 한다.

예제 3: SQL에 적용해 보자

-- alphabet_cte를 선언한 후 재귀 알고리즘을 통해 얻은 결과를 합쳐서 보여준다.
/* 특이한 점은 영구적인 테이블이나 뷰가 아닌, 해당 쿼리 실행 중에만 존재하는 임시 결과 집합이라는 것이다. */
WITH RECURSIVE alphabet_cte AS (
    -- 예제 1: 'A'(65)로 시작하는 실제 테이블의 행을 직접 조회하기 위한 기저값 생성
    SELECT 
        65 AS ascii_val,
        CHR(65) AS ch
    
    -- 각 재귀 깊이 별로 조회한 결과 전체(ALL)를 UNION 연산하여(모두 모아서) 보여준다.
    -- 즉 'A%'부터 'Z%'까지 모두 합친다.
    UNION ALL

    -- 예제 2-3: 다음 알파벳(ascii_val + 1)으로 시작하는 행을 결합하기 위해 값 순차 증가
    SELECT 
        ascii_val + 1,
        CHR(ascii_val + 1)
    FROM alphabet_cte
    /* 주의: 일반적인 프로그래밍 언어와 달리, 함수 내에서 평가 후 exit이 아니다.
     * ASCII CODE 89에서 재귀 호출 함수(89+1)을 하기 때문에, 89까지만 "재귀 깊이를 내려가게 하여"
     * 자연스럽게 65..90을 얻어야 한다.
     */
    WHERE ascii_val < 90
)

-- 아스키 값과 대상 컬럼을 기준으로 실제 테이블과 조인하여 정렬, 조회한다.
SELECT 
    a.ascii_val,
    a.ch,
    t.*
FROM alphabet_cte a
JOIN my_table t
  ON t.target_column LIKE a.ch || '%'
ORDER BY a.ascii_val, t.target_column;

이 예제는 INNER JOIN이나, 만약 데이터가 없어도 A~Z 26개 문자를 모두 보존하고 우측을 NULL로 두고 싶다는 요구사항이 생긴다면 LEFT JOIN으로 바꾼다.

예상 결과

만약 my_table에 값들이 있다고 치면, 대략 이런 식으로 나오게 될 것이다.

#	ascii_val	ch	target_column
1	65	A	Air
2	65	A	Apple
3	66	B	Banana
4	68	D	Duck
5	90	Z	Zebra

P.S

자격증을 공부하면서 SQL을 복습하고 있는데, 교재는 너무 프로그래밍적인 실체를 생략하고 있었다.

SQL은 암기하는 것이 아니고, 배워 익히는 것이다. 단순히 자격증을 외워서 통과하기만 하면 된다는 마음가짐으로는 SQLD를 얻어도 실무에서 좋은 인상을 얻기는 어렵다.

개인적으로는 체용(體用)이라는 개념을 매우 좋아한다. 앞으로도 아래의 개념을 기반으로 공부 내용을 정리할 것이다.

실체를 알아야 쓰임을 안다.