PostgreSQLの再帰SQL（1）
――再帰SQLの構文

20から30までの自然数を出力など

　本連載ではPostgreSQL 8.4を使って、再帰SQLの入門事項や、Oracle Technology Networkでよく見かける問題の解法や、Oracleの階層問い合わせや再帰with句の機能を模倣する方法を、SQLのイメージを交えて解説します。本稿では、再帰SQLの構文などを扱います。

はじめに

　本連載ではPostgreSQL 8.4を使って、再帰SQLの入門事項や、Oracle Technology Network（OTN）でよく見かける問題の解法、Oracleの階層問い合わせや再帰with句の機能を模倣する方法を、SQLのイメージを交えて解説します。

　本稿では、再帰SQLの構文などを扱います。

対象読者

• PostgreSQLで再帰SQLを使ってみたい方
• 再帰SQLの理解を深めたい方

必要な環境

　本稿で扱うSQLは、PostgreSQL 8.4 beta 2で動作確認しました。その他、次の環境でも応用が可能です。

• Oracle 11g R2以降
• DB2
• SQL Server 2005以降

1. 再帰SQLとは

　with句でselect文を実行して、そのselect文の結果を使ってselect文を実行して、そのselect文の結果を使ってselect文を実行して、……（以下続く）といったことを行うSQLです。

　PostgreSQL 8.4やOracle 11g R2以降やSQL Server 2005以降などで使用できます。再帰SQL、再帰クエリ、共通表式、CTE、再帰with句などと呼ばれます。再帰SQLの使い道としては、階層構造やグラフ構造のデータに対するデータ取得、行の補完、行間計算などが挙げられます。

2. with句（再帰なし）

　まずは、再帰のないwith句の構文に慣れておきましょう。再帰のないwith句の代表的な使い道としては、テスト用の仮想テーブルの作成です。ちょっとSQLを試したい時などに使います。下記がサンプルです。

仮想テーブルの作成と使用の例1

with tmp(ID,Val) as(
select 111,1 union all
select 222,1 union all
select 333,1)
select ID,Val from tmp;

出力結果

ID	Val
111	1
222	1
333	1

仮想テーブルの作成と使用の例2

with tmp(ID,Val) as(
values(444,2),
      (555,2),
      (666,2))
select ID,Val from tmp;

出力結果

ID	Val
444	2
555	2
666	2

　下記のようにインラインビューでも仮想テーブルを作れますが、with句であれば、SQLの最初の数行に仮想テーブルの値が集中し、編集が容易なため、with句がよく使われます。

インラインビューで仮想テーブル作成

select ID,Val
  from (values(444,2),
              (555,2),
              (666,2)) as tmp(ID,Val);

　複雑なselect文の結果同士での自己結合が必要なケースでwith句が使われることもあります。下記の例のように、with句の中に複雑なselect文を記述すれば、複雑なselect文は1回しか記述しなくて済みます。

with句の中に複雑なselect文を記述

wlth tmp as(
select ID,sum(Val) as sumVal
  from anyTable
 group by ID
having max(Val) = 100)
select a.ID,a.sumVal,b.ID,b.sumVal
  from tmp a,tmp b
 where a.sumVal <= b.sumVal;