WITH (Common Table Expressions)

이 MySQL 문서와 이 튜토리얼을 읽고 얻은 지식을 정리하는 포스트다.

WITH

WITH란 키워드 자체는 들어본 적도, 써본 적도 없기 때문에 프로그래머스에서 SQL Kit 문제를 풀다가 처음 만났을 때 당혹스러웠다. 항상 모든 쿼리는 한 줄로 SELECT로 시작해서 조건으로 끝난다고 막연히 생각하고 있었는데 실제로는 더 복잡한 쿼리를 사용할 수도 있겠다는 느낌을 받았다.

 

WITH 명령어의 목적은 해당 쿼리가 실행되기 전에 쿼리에서 참조할 수 있는 임시 테이블을 만드는 것이다. 이 임시 테이블 역시 특정 쿼리(서브 쿼리)의 결과로 생성되어야 하는데 적절하진 않지만 기존의 테이블을 활용하여 예시를 보이면 다음과 같다.

MariaDB [vulnerable_db]> WITH MY_COMPANY AS (SELECT "Koreatech")
    -> SELECT * FROM USER_TABLE WHERE COMPANY=(SELECT * FROM MY_COMPANY);
+------+---------+-----------+-------+
| id   | name    | company   | SCORE |
+------+---------+-----------+-------+
|    1 | Kwonkyu | Koreatech |    50 |
|    8 | Jaemin  | Koreatech |    90 |
+------+---------+-----------+-------+
2 rows in set (0.000 sec)

먼저 SELECT 명령으로 본 쿼리를 실행하기 전에 WITH 명령어로 MY_COMPANY라는 테이블을 만든다. 이 테이블은 AS 뒤에 있는 (SELECT "Koreatech")라는 서브 쿼리를 기반으로 생성된다. 그 결과 "Koreatech"이라는 문자열 하나만 포함된 MY_COMPANY 테이블이 생성되며 이는 현재 쿼리에서만 참조 가능하다. 그리고 본 쿼리에서 WHERE 조건절에 'COMPANY' 칼럼 값이 (SELECT * FROM MY_COMPANY) 서브 쿼리의 결과와 동일한 레코드만 출력하도록 지정한다. 현재 MY_COMPANY 테이블에는 "Koreatech"이라는 레코드만 담겨 있기 때문에 이는 다음과 같은 쿼리와 동일하다.

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE WHERE COMPANY="Koreatech";
+------+---------+-----------+-------+
| id   | name    | company   | SCORE |
+------+---------+-----------+-------+
|    1 | Kwonkyu | Koreatech |    50 |
|    8 | Jaemin  | Koreatech |    90 |
+------+---------+-----------+-------+
2 rows in set (0.000 sec)

좀 더 정확한 예시를 위해 다음과 같은 테이블 두 개를 생성했다.

MariaDB [vulnerable_db]> SELECT * FROM SCORE_TABLE;
+----+---------+-------+
| ID | NAME    | SCORE |
+----+---------+-------+
|  1 | Kwonkyu |    55 |
|  2 | Jason   |    75 |
|  3 | Prodo   |    77 |
|  4 | Sauron  |    99 |
|  5 | Saruman |   100 |
|  6 | John    |    56 |
|  7 | Tyler   |    80 |
|  8 | Jaemin  |    74 |
+----+---------+-------+
8 rows in set (0.000 sec)

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE;
+------+---------+-----------------------------+-------+
| id   | name    | company                     | SCORE |
+------+---------+-----------------------------+-------+
|    1 | Kwonkyu | Koreatech                   |    50 |
|    2 | Jason   | Shadow Company              |    55 |
|    3 | Prodo   | The Fellowship of the Rings |    35 |
|    4 | Sauron  | The Fellowship of the Rings |    80 |
|    5 | Saruman | The Fellowship of the Rings |    76 |
|    6 | John    | Shadow Company              |    70 |
|    7 | Tyler   | Shadow Company              |    77 |
|    8 | Jaemin  | Koreatech                   |    90 |
+------+---------+-----------------------------+-------+
8 rows in set (0.000 sec)

두 테이블은 동일한 'id', 'name' 값을 가진 레코드로 구성되어 있으며 서로다른 'SCORE' 칼럼 값을 가지고 있다. 여기서 'USER_TABLE' 테이블의 'SCORE' 값을 원래 점수(ORIGINAL SCORE), 'SCORE_TABLE'의 'SCORE' 값을 새로운 점수(NEW SCORE)라고 해볼 때 WITH를 사용하여 두 테이블을 다른 이름으로 참조하며 점수를 합산한 결과를 출력하는 쿼리를 작성해볼 수 있다.

MariaDB [vulnerable_db]> WITH ORIGINAL_SCORE AS (SELECT * FROM USER_TABLE),
    -> NEW_SCORE AS (SELECT * FROM SCORE_TABLE)
    -> SELECT ORIGINAL_SCORE.NAME, ORIGINAL_SCORE.SCORE+NEW_SCORE.SCORE
    -> FROM ORIGINAL_SCORE, NEW_SCORE
    -> WHERE ORIGINAL_SCORE.ID=NEW_SCORE.ID;
+---------+--------------------------------------+
| name    | ORIGINAL_SCORE.SCORE+NEW_SCORE.SCORE |
+---------+--------------------------------------+
| Kwonkyu |                                  105 |
| Jason   |                                  130 |
| Prodo   |                                  112 |
| Sauron  |                                  179 |
| Saruman |                                  176 |
| John    |                                  126 |
| Tyler   |                                  157 |
| Jaemin  |                                  164 |
+---------+--------------------------------------+
8 rows in set (0.000 sec)

위 쿼리에서는 먼저 USER_TABLE, SCORE_TABLE 각각의 모든 레코드를 ORIGINAL_SCORE, NEW_SCORE라는 임시 테이블에 저장하고 있다. 그리고 본 쿼리에서 ORIGINAL_SCORE 테이블의 ID 칼럼과 NEW_SCORE 테이블의 ID 칼럼이 같은 레코드에 대해서 ORIGINAL_SCORE 테이블의 SCORE 칼럼과 NEW_SCORE 테이블의 SCORE 칼럼을 합한 결과를 ORIGINAL_SCORE 테이블의 NAME 칼럼 값과 출력하고 있다.

 

즉 두 테이블에서 같은 ID의 레코드끼리 SCORE를 더해서 NAME과 같이 출력하고 있는 것이다. 테이블 이름이 길어져서 쿼리가 좀 길어지긴 했지만 결과적으로는 올바른 결과를 낸 것을 볼 수 있다. 지금은 두 테이블의 모든 레코드를 그대로 임시 테이블로 불러왔지만 추후 필요에 따라 각종 조건(일정 점수 이상이라던지)을 적용하여 임시 테이블을 생성할 수도 있을 것이다.

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE WHERE SCORE >= 80;
+------+--------+-----------------------------+-------+
| id   | name   | company                     | SCORE |
+------+--------+-----------------------------+-------+
|    4 | Sauron | The Fellowship of the Rings |    80 |
|    8 | Jaemin | Koreatech                   |    90 |
+------+--------+-----------------------------+-------+
2 rows in set (0.000 sec)

MariaDB [vulnerable_db]> SELECT * FROM SCORE_TABLE WHERE SCORE >= 80;
+----+---------+-------+
| ID | NAME    | SCORE |
+----+---------+-------+
|  4 | Sauron  |    99 |
|  5 | Saruman |   100 |
|  7 | Tyler   |    80 |
+----+---------+-------+
3 rows in set (0.000 sec)

MariaDB [vulnerable_db]> WITH ORIGINAL_SCORE AS (SELECT * FROM USER_TABLE WHERE SCORE >= 80),
    -> NEW_SCORE AS (SELECT * FROM SCORE_TABLE WHERE SCORE >= 80)
    -> SELECT ORIGINAL_SCORE.NAME, ORIGINAL_SCORE.SCORE+NEW_SCORE.SCORE
    -> FROM ORIGINAL_SCORE, NEW_SCORE
    -> WHERE ORIGINAL_SCORE.ID=NEW_SCORE.ID;
+--------+--------------------------------------+
| name   | ORIGINAL_SCORE.SCORE+NEW_SCORE.SCORE |
+--------+--------------------------------------+
| Sauron |                                  179 |
+--------+--------------------------------------+
1 row in set (0.001 sec)

위의 쿼리에서는 원래 점수, 새로운 점수가 모두 80점 이상인 레코드에 대해서만 합산한 결과를 계산하기 위해 USER_TABLE, SCORE_TABLE에서 SCORE 컬럼값이 80 이상인 레코드만 조회해서 ORIGINAL_SCORE, NEW_SCORE로 저장하고 있다. 그렇기 때문에 ORIGINAL_SCORE.ID=NEW_SCORE.ID에 해당되는 레코드는 Sauron밖에 없기 때문에 Sauron의 점수만 합산되어 쿼리의 결과로 출력된 것을 볼 수 있다.

RECURSIVE

이렇게 WITH로 작성된 임시 테이블은 CTE, 즉 Common Table Expressions라고 한다. 이 CTE를 생성할 때는 재귀적인 방법을 사용할 수도 있는데 'WITH RECURSIVE' 처럼 'RECURSIVE' 옵션을 붙어서 생성하면 된다. 재귀라는 특성 답게 임시 테이블을 생성하는 서브쿼리에서 CTE 자체를 호출하는 구문과 탈출 조건이 포함되어야 한다. 문서에 나와있는 예시 쿼리를 실행시켜보면 다음과 같은 결과를 얻을 수 있다.

MariaDB [vulnerable_db]> WITH RECURSIVE cte (n) AS
    -> (
    ->   SELECT 1
    ->   UNION ALL
    ->   SELECT n + 1 FROM cte WHERE n < 5
    -> )
    -> SELECT * FROM cte;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.001 sec)

WITH RECURSIVE 명령어를 통해 RECURSIVE 방식으로 생성할 CTE인 'cte'에 대해 컬럼 'n'을 설정해주고 AS 명령어를 이용해 특정 쿼리를 수행하도록 지정한다. 이때 쿼리 내부에서는 'UNION', 'UNION ALL' 또는 'UNION DISTINCT'로 쿼리를 두 가지로 분리해야 한다. 분리자의 앞에 있는 쿼리는 일종의 초기 쿼리(initial query)이며 현재 CTE를 재귀 호출하지 않는다. 뒤에 있는 쿼리는 재귀 호출 시 실제로 호출되는 재귀 쿼리(recursive query)로 현재 CTE를 재귀 호출한다. 위의 예제에서는 'SELECT 1'이 재귀를 시작할 때 한번만 호출되는 초기 쿼리이며 'SELECT n + 1 FROM cte WHERE n < 5'이 재귀 시마다 호출되는 재귀 쿼리에 해당한다.

 

초기 쿼리에서는 이 CTE가 기반으로 사용할 레코드를 생성한다. 이후 재귀 쿼리에서 이 레코드에 새로운 레코드를 추가하는데 이때 UNION 명령어를 사용하여 두 SELECT(초기 쿼리와 재귀 쿼리 또는 재귀 쿼리와 재귀 쿼리)의 결과를 합치면서 추가해나가는 것이다. 위의 예제에서는 컬럼 'n'에 대하여 'n < 5'라는 조건을 WHERE에 걸어두었기 때문에 n이 4까지만 동작하고 재귀가 멈추게 된다. 이때 재귀 쿼리에서 'SELECT n + 1'을 하기 때문에 4 + 1인 5까지 출력되는 것이다.

 

결국엔 'SELECT 1 UNION ALL SELECT 1 + 1 UNION SELECT 1 + 1 + 1 ...'같은 쿼리나 마찬가지라고 할 수 있다. 자기 자신(cte)를 호출하면서 계속 SELECT하면서 레코드를 쌓아가고 있으며 이를 UNION ALL로 합쳐서 cte라는 임시 테이블로 생성하는 것이다. CTE를 정의할 때 컬럼을 지정하지 않고 다음처럼 초기 쿼리에서 임시 테이블의 컬럼명을 지정할 수도 있다.

MariaDB [vulnerable_db]> WITH RECURSIVE cte AS (
    -> SELECT 1 AS NUMBER_STACK
    -> UNION ALL
    -> SELECT NUMBER_STACK + 1 FROM cte
    -> WHERE NUMBER_STACK < 5
    -> )
    -> SELECT * FROM cte;
+--------------+
| NUMBER_STACK |
+--------------+
|            1 |
|            2 |
|            3 |
|            4 |
|            5 |
+--------------+
5 rows in set (0.000 sec)

초기 쿼리에서 임시 테이블의 컬럼명을 정의한 것을 볼 수 있다. 이 경우 재귀 쿼리에서도 해당 컬럼명을 사용해야 한다.

 

 

이 WITH에 대해서 알게 된 건 프로그래머스의 SQL Kit에서 풀던 오랜 기간 보호한 동물(2) 문제였다. 생각보다 SQL 문제도 재밌는게 많아서 열심히 풀었는데 GROUP BY의 마지막 문제인 입양 시각 구하기(2) 문제에서는 WITH와 더불어 RECURSIVE를 활용하는 풀이가 있어서 이걸 읽고 이해하는 것만으로도 공부가 많이 됐다.

코딩테스트 연습 - 오랜 기간 보호한 동물(2)

코딩테스트 연습 - 입양 시각 구하기(2)