SQL Server 再帰クエリ入門

通常のプログラム言語では For 文や Do While 文などの繰り返し処理をする命令が存在します。
しかし、通常のSQLクエリ文にはこれらの命令は存在しません。

例えば1月1日から12月31日までのカレンダーテーブルを作成するとか、上司・部下の関係などのツリー構造を
辿ってデータ取得するような繰り返し処理をするような場合に、再帰クエリが有効です。

再帰クエリを習得しておけば通常のSQLクエリでは処理できないようなことも可能になります。
再帰クエリは一見すると混乱すような記述になっていますが、一度仕組みを理解すれば以降は簡単に理解できるようになると思います。

この記事では再帰クエリを自力で作成できるように、基本から解説しますので、是非一読してご自分でも再帰クエリを活用してみて下さい。

まず WITH 句を理解する

SQL Server では WITH句を使って再帰クエリを構築 します。ですから、まず WITH句について理解しましょう。
とは言っても難しいことはありませんので、安心して進んで下さい。

WITH 句とは？

SQL Server の WITH 句（Common Table Expression：CTE）は、一時的なクエリに名前をつけて、それをクエリ内で再利用できる機能です。結果としてクエリを簡潔にして可読性を向上させることができます。

WITH 句の基本構文

WITH句の基本構文は以下のようになっています。

WITH CTE名
AS (
    -- "CTE名" という名前をつけるクエリの記述
    SELECT ...
)
-- 後から CTE名 を呼び出して利用する
SELECT * FROM CTE名;

例：基本的な CTE の使用

以下の例では、売上データから売上の多い上位 3 人の社員を取得します。CTEを作成しておくことで、最後のクエリはシンプルにすることができます。

WITH SalesCTE AS (
    SELECT EmployeeID, SUM(SalesAmount)
    FROM Sales
    GROUP BY EmployeeID
)
SELECT TOP 3 * FROM SalesCTE ORDER BY TotalSales DESC;

CTE のメリット

(1) クエリの可読性向上

サブクエリを使用する場合に比べ、SQL の可読性が向上します。

(2) 再利用可能な論理構造

1 回の CTE 定義で、同じクエリ内の複数の箇所で CTE を参照できます。

(3) 再帰クエリのサポート

CTE は 自己参照（再帰的なクエリ） をサポートしており、本記事でご紹介する再帰クエリを構築可能です。

再帰クエリの形

再帰クエリは前の項で解説した WITH句を用いてCTEを作成することで構築できます。

文字通り、再び帰ってくる クエリで、CTEの定義の中に自分自身の名前（CTE名）が呼び出されます。

再帰クエリは図のような形をしています。

1つ目のクエリ：最初に実行される起点となるクエリ

1つ目のクエリは最初に実行される起点となるクエリで、このクエリの結果が空であれば、起点が無いので、この再帰クエリの出力も空になってしまいます。

一方、１つでも出力結果があれば、これが起点となって次々と処理を始めることができます。

図では1つ目のクエリ 2025年1月1日 を出力しますので、これが起点となります。

2つ目のクエリ： CTE定義の中で呼び出されるCTEの動作とは？

‘union all’ で1つ目のクエリと2つ目のクエリを繋いでいます。

2つ目のクエリを見ると不思議なことに CTE定義の中であるにもかかわらず、CTE自身の名前が呼び出されています。

これは一体どういう動作をするのでしょうか？

結論から申し上げますと、CTEの前回の処理結果が呼び出される ということになります。

つまり、以下のようにして処理が進んでいくのです。

1回目の処理： 1つ目のクエリが処理されて、2025年1月1日 を出力
2回目の処理： 2つ目のクエリが処理されて、Calendar_2025 の出力は前回の処理結果である 2025年1月1日となるが、クエリでは DATEADD関数で1日足しているので、2025年1月2日 が出力される。
3回目の処理：3回目以降は2つ目のクエリが処理され続けます が、前回の処理結果は 2025年1月2日で、再びDATEADD関数で1日足して 2025年1月3日 を出力する

ここで重要なのは、1つ目のクエリは最初に1回だけ実行されるのに対して、2つ目のクエリは終了条件を満たすまで何度も繰り返して実行される という点です。

終わりを指定する（WHERE句）

前回の処理結果の日付に1日足して出力するという処理は永遠に繰り返すことができてしまいます。

そのままでは 無限ループになってしまう ので、このクエリではWHERE句で終わりの条件、つまり2025年12月31日より小さい
という条件を加えています。

ちなみに、条件は2025年12月31日自体は含んでいないように見えますが、クエリで1日足しているのでやはり2025年12月31日まで
出力されることになります。

SQL Server の再帰の繰り返し回数制約を変更

無限ループを実行してしまうとコンピュータのリソースを無駄に消費してしまいますので、SQL Server では再帰の繰り返し回数を
制限しています。デフォルト値は 100回 となっています。

今回のようにカレンダーを出力する場合は 364回（1つ目クエリは再帰では無いので、回数から除外して 365 – 1 = 364回）
繰り返し処理をしますので、OPTION (MAXRECURSION 364) というオプション指定を加えています。

しかし、ここまでキッチリ指定しなくても、 1000回 程度に大きめに設定しておくことで良いかと思います。

ツリー構造データを辿ってデータ取得

再帰クエリの活用で良くあるのがツリー構造データの処理です。

例として図のようなデータ（テーブル）があったとします。

ここで、出木杉部長を含む配下全員 の社員をリストアップする必要が出てきた場合、どのようなクエリを組めば良いでしょうか？

これは通常のクエリでは処理ができず、再帰クエリを活用する必要があります。再帰クエリで次のような処理をします。

まず最初に出木杉部長自身をリストアップ
次に出木杉部長を上司とする配下社員をリストアップ
更に次では上記2．の社員を上司とする配下社員をリストアップ
以下、同様・・・

なんとなく、再帰クエリが使えそうな気がしてくると思います。

では再帰クエリを見ていきましょう。

with 配下社員 as 
(
    select * from 社員DB where 社員ID = 2

    union all

    select a.* from 社員DB a inner join 配下社員 b on b.社員ID = a.上司社員ID
)

select * from 配下社員

まず、WITH句を使って 配下社員 という CTEを定義します
1つ目のクエリでは社員ID が “2” の出木杉部長をリストアップします
2つ目のクエリは繰り返し処理されるものですが、前回の処理結果（リストアップされた社員）を上司とする社員をリストアップすれば良いので、配下社員 のCTE名を呼び出してテーブル結合しています

SQL Server Management Studio（SSMS）での実行結果は図のようになりました。正しく配下社員を出力できていることが分かります。

実は簡単ではない再帰クエリのソートの対策

前の例で配下の社員をリストアップする際に社員氏名でソートしたい場合、ORDER BY 句 でソートすれば良いように思えます。

しかし、実行してみると図のようにエラーが発生してしまいます。ORDER BY 句 は CTE定義の中で使えないのです。

何か他のものでソートができれば良いですが、例えば再帰クエリの出力全体を社員氏名でソートすると、下の図のように上下の関係が崩れてしまい、誰の配下であるかが分からないものになってしまいます。

解決策：ソートキーを作成する

この課題を解決するために、再帰クエリにソートするためのキー文字列を追加します。

文章の章立てと同じように、以下のようなキー文字列が作成できると良い訳です。

ソートキーをSQLで実装する

思い浮かべるのは簡単ですが、実際にSQLのクエリで実現するにはどうしたら良いでしょうか？

少々複雑になりますが、まずは回答から示します。

with 配下社員 as 
(
    select 
    cast(format(row_number() over (order by 社員氏名 asc), '0000') as nvarchar(max)) as 階層パス,
    * from 社員DB where 社員ID = 2

    union all

    select 
    cast(b.階層パス + '-' + format(row_number() over (order by a.社員氏名 asc), '0000') as nvarchar(max)) as 階層パス,
    a.* from 社員DB a inner join 配下社員 b on b.社員ID = a.上司社員ID 
)
select * from 配下社員 order by 階層パス

ここで、1つ目のクエリと2つ目のクエリに cast から始まる 階層パス の列を追加しています。

階層パスのクエリの中身を解説します。入れ子の中身から順に理解すると理解が早いです。

row_number() over (order by XXXX asc) はクエリ出力の行番号を出力する関数で、この関数の中の order by 句 はCTE定義やビューの定義の中で使ってもエラーにはなりませんので、これを活用しています。
行番号をそのまま文字列で繋いでしまうと、桁が上がった時にソート順がおかしくなるので、0001, 0002, 0003 … というようにゼロ埋めした文字列になるように format 関数 を使っています。format関数の　‘0000’ の部分はデータの行数に合わせて増減して下さい。この例では1階層に1万件まで対応できるので、十分過ぎるほど大きいです。
最後に cast 関数 でデータ型を nvarchar(max) に変換しています。これは文字列で文字数最大を指定したデータ型で、これなら例えば階層が10階層になっても対応できそうです。データ型を1つ目のクエリと2つ目のクエリで同じにしておかないとエラーになるので、この設定も必要になります。同じ文字列型でも文字数がズレただけでエラーになりますので注意が必要です。

実行結果は図のようになりました。元のデータの順から変わって氏名でソートされた結果になっています。

ちなみに、この仕組みを使えば先ほど出てきた文章の章番号のようなものを作成するのも簡単にできますので、ニーズがあれば活用できると思います。

まとめ

一見混乱してしまいそうな再帰クエリの作成について基本から解説させて頂きました。

ご紹介した通り、カレンダーのように連続したデータや、ツリー構造のデータを辿ってデータを取得していくような用途に再帰クエリを活用することができます。

仕事をしていると意外にも再帰クエリが必要になる場面が出てきますので、是非ご自分で動かしてみてスキルを習得してみて下さい。
ある時再帰クエリに大いに助けられる時が来るかも知れませんよ。