[SQL] 再帰クエリでツリー（木）構造を扱う

SQLにおける再帰的な処理

リレーショナルデータベース(RDB)には、階層的なデータ構造(例：ツリー構造など)を扱うことができる、再帰クエリをサポートしているものがあります。再帰クエリを使の使い方を以下に紹介します。

再帰クエリをサポートするRDB

Wikipediaによると以下のRDB(バージョンによっては利用不可)は再帰クエリを使えるようです。今回は以下のうち、SQLServer と SQLite3 での例を示します。

• Microsoft SQL Server
• PostgreSQL
• Oracle Database
• SQLite3

再帰クエリのサンプル

ツリーデータの例

今回使うデータは次のような典型的なツリーとします。テーブル構造としては、親への参照を持つことにします。

データ作成SQL

CREATE TABLE tree (id int, parent_id int)

INSERT INTO tree (id, parent_id) VALUES
(1, null)
, (2, 1)
, (3, 1)
, (4, 1)
, (5, 2)
, (6, 2)
, (7, 3)
, (8, 7)

SQL Serverの例

SQL Server では、共通テーブル式 (CTE)を使用することで再帰クエリを実現できます。例えばid = 1の子孫ノードすべてを取得するSQLは次のようになります。

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

SQLite3

SQLite3でも、再帰クエリ (WITH RECURSIVE) を使えます。

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

参考URL

• 共通テーブル式を使用する再帰クエリ(SQL Server)
• 再帰クエリ