SQL WITH句で自己結合を再帰的に問い合わせる

SQL WITHクエリ、今まで使ったことがなかったのでメモしておきます。

自己結合を再帰的に問い合わせる時に使用します。

再帰のイメージ

WITH句で仮想テーブルを作成し、再帰クエリでUNIONしていくイメージです。

TREEテーブル表

id	parent_id
1	null
10	null
2	1
3	2
4	2
5	4
11	10

WITH句構文

WITH RECURSIVE [仮想テーブル名] AS (
    [ベースとなるクエリ rの最初の仮想テーブルとなる]
    UNION ALL
    [再帰クエリ。仮想テーブルrにレコード追加するクエリ。仮想テーブル自体も参照できる]
)
SELECT * FROM [仮想テーブル名]

実装例

TREEテーブルの再帰例。id=1からparent_idを辿っていく。

WITH RECURSIVE  r AS (
    SELECT * FROM tree WHERE id = 1
    UNION ALL
    SELECT tree.* FROM tree, r WHERE tree.parent_id = r.id
)
SELECT * FROM r

r仮想テーブルの生成のイメージ

ベースクエリ

id	parent_id
1	null

1回目のUNION

id	parent_id
1	null
2	1

2回目のUNION

id	parent_id
1	null
2	1
3	2
4	2

3回目のUNION

id	parent_id
1	null
2	1
3	2
4	2
5	4

のように再帰クエリの条件に一致する行がなくなるまで再帰してUNIONされる。

UNIONされた順でORDER BYする

WITH RECURSIVE  r AS (
    SELECT *, 1 as n FROM tree WHERE id = 1
    UNION ALL
    SELECT tree.*, n + 1 FROM tree, r WHERE tree.parent_id = r.id
)
SELECT * FROM r ORDER BY n [ASC | DESC]

参考にしたページ。

[SQL] 再帰クエリでツリー（木）構造を扱う │ Web備忘録

再帰SQL | Let's Postgres