SQLの再帰について原理から使い方まで徹底解説！【WITH句】

SQLの再帰とは？

ここでは、SQLにおける「再帰処理」について紹介します。

再帰処理とは、再帰呼び出しと同じ意味合いの言葉です。

「じゃあ”再帰呼び出し”って何ですか？」と思う人もいるかと思います。

再帰呼び出し（再帰処理）は、自分自身を呼び出す処理・関数を呼び出して実行する処理のことです。

自分自身の処理を呼び出すと、また次も呼び出して、その次も…と繰り返しになるので「再帰的」と言えます。

SQLで再帰処理を用いた構文を「再帰SQL」とも呼ぶのです。

意味合いは同じで、再帰処理（ループ）を実現できます。

再帰SQLは、前回の処理結果を使って同様の処理を繰り返すケースで使うのです。

例えば、あるテーブルが格納しているデータが木構造の場合、データの根から葉までをたどるようなイメージになるでしょう。

ポテパンダの一言メモ

再帰SQLは、自分自身を呼び出す処理のこと！

自分自身を呼び出すので、ループ・繰り返し処理を実現できます。

再帰SQLの使い方【WITH RECURSIVE】

再帰SQLを利用するには「WITH句（WITH RECURSIVE句）」を使用します。

WITH句は、1つのSQL文の中で共通したテーブルを簡略的に呼び出せる句です。

WITH句を使用すると、サブクエリに名前をつけることできます。

そのため、メインクエリから何度でも呼び出し可能になるのです。

通常、RECURSIVE句は省略できますが、PostgreSQLでは省略できません。

では、サンプルを用いてWITH句の使い方を確認してみましょう。

次のような sample_tableテーブル を用意しました。

mysql> select * from sample_table;
+------+-----------+-------+
| id   | name      | price |
+------+-----------+-------+
|  101 | Product_A |  1300 |
|  102 | Product_B |  1500 |
|  103 | Product_C |  2100 |
|  104 | Product_D |  1800 |
|  104 | Product_E |  1200 |
+------+-----------+-------+
5 rows in set (0.00 sec)

上記 sample_tableテーブル の nameカラム を取り出す再帰SQLをWITH句を使って実行してみましょう。

次のように記述します。

WITH product_name AS ( SELECT name FROM sample_table) SELECT * FROM product_name;

上記SQLは、sample_tableテーブルから nameカラム を取り出した「product_name」という再帰共通表式を作成しました。

これによって、WITH句から始めるSQL文で「product_name」という再帰共通表式を何度でも使えます。

そのため、サブクエリを使って同じテーブル何度も呼び出しする必要がなく、可読性が高まるのです。

上記の再帰SQLを実行すると、次のような結果を取得できます。

mysql> WITH product_name AS ( SELECT name FROM sample_table) SELECT * FROM product_name;
+-----------+
| name      |
+-----------+
| Product_A |
| Product_B |
| Product_C |
| Product_D |
| Product_E |
+-----------+
5 rows in set (0.00 sec)

期待した通りに、nameカラム だけを取得できました。

では、もう少し複雑な例で確認してみます。

先ほどのテーブルとは別に score_tableテーブル を用意し、次のようにデータを追加しましょう。

mysql> select * from score_table;
+------+--------+-------+------------+
| id   | name   | score | dt         |
+------+--------+-------+------------+
|    1 | Oda    |   143 | 2020-05-01 |
|    2 | Satou  |   176 | 2020-05-01 |
|    3 | Yamada |   155 | 2020-05-01 |
|    1 | Oda    |   164 | 2020-06-01 |
|    2 | Satou  |   181 | 2020-06-01 |
|    3 | Yamada |   149 | 2020-06-01 |
|    1 | Oda    |   178 | 2020-07-01 |
|    2 | Satou  |   172 | 2020-07-01 |
|    3 | Yamada |   166 | 2020-07-01 |
+------+--------+-------+------------+
9 rows in set (0.00 sec)

では、この中で3ヶ月間のスコア合計が一番高い人を確認してみます。

次のように記述しましょう。

WITH total_score (name, score) 
AS 
(SELECT name, SUM(score) 
FROM score_table 
GROUP BY name) 
--メインクエリ
SELECT t1.name, t1.score 
FROM total_score t1 
WHERE t1.score 
    = (SELECT MAX(t2.score) 
        FROM total_score t2);

まず、WITH句で「total_score」という再帰共通表式を作成しました。

score_table の name と score カラムを取得しています。

この時、scoreにはGROUP BY句で nameごとにscoreを合計しています。

これで、各人ごとの3ヶ月分のスコア合計を取得しているのです。

そして、メインクエリで、合計スコアが高い人をMAX関数とWHERE句で条件指定しています。

上記の再帰SQLを実行すると、次の結果が取得できるはずです。

mysql> WITH total_score (name, score) AS (SELECT name, SUM(score) FROM score_table GROUP BY name) SELECT t1.name, t1.score FROM total_score t1 WHERE t1.score = (SELECT MAX(t2.score) FROM total_score t2);
+-------+-------+
| name  | score |
+-------+-------+
| Satou |   529 |
+-------+-------+
1 row in set (0.00 sec)

なお、各人の合計スコアを取得する再帰SQLを確認してみると次の通りです。

mysql> WITH total_score (name, score) AS (SELECT name, SUM(score) FROM score_table GROUP BY name) SELECT name, score FROM total_score;
+--------+-------+
| name   | score |
+--------+-------+
| Oda    |   485 |
| Satou  |   529 |
| Yamada |   470 |
+--------+-------+
3 rows in set (0.00 sec)

このように、ちゃんと期待した動きになっているのがわかりますね。

ここまでWITH句の使い方について紹介しました。

WITH句の使い方は次の記事でも解説しているので、よければ参考にしてみてください！

【関連記事】

▶︎SQLのWITH句で可読性アップ！再帰共通表式でサクッとSQL文を書く

各DBMSの再帰SQL

ここでは、各DBMSの再帰SQLについて簡単に紹介します。

MySQLの場合

「再帰SQLの使い方【WITH RECURSIVE】」で紹介したように、WITH句を使えばOKです。

PostgreSQLの場合

PostgreSQLの場合は、WITH RECURSIVE句を使用しましょう。

基本的な使い方は、MySQLと同様です。

詳しくは、次のサイトがわかりやすく説明しているので参考になると思います。

※参考：再帰SQL | Let’s POSTGRES

Oracleの場合

OracleもWITH句を使って再帰SQLを実現します。

以下のOracleブログが体系的に説明しているので、理解が進むはずです！

※参考：図でイメージするOracle DatabaseのSQL全集　第7回 再帰with句