SQL WITH句再帰クエリー RECURSIVE SQLポケリ

2016年3月6日 2025年4月22日 asai

asai

最近、SQLネタをブログにアップしている。本日もSQLネタである。

WITH句

少し前から、WITH句というものが使えるようになっている。
Oracleだと9iから使えるのか。割と昔からあるじゃない。SQLポケリには載っていない。
(改訂第４版には載ってます)。
WITHを使うと、長ったらしいサブクエリを何回も書かなくてよくなる。
これは便利。
CREATE VIEWをSELECT命令内でやってしまうようなもの。
具体例を示しながら解説してみる。

SELECT foo.a, bar.b FROM foo INNER JOIN bar ON foo.a = bar.a

といったSELECT命令をサブクエリで何回も使いたいとする。
ビューを定義してしまえば、話は簡単。

CREATE VIEW vfoobar AS
SELECT foo.a, bar.b FROM foo INNER JOIN bar ON foo.a = bar.a

vfoobarという名前のビューを定義した。
vfoobarをテーブルのように使用できるから、長ったらしいfooとbarをa列で結合して...といったSELECT命令をvfoobarという名前だけで使用できるようになる。

SELECT * FROM vfoobar
WHERE a = (SELECT MIN(a) FROM vfoobar)
OR  a = (SELECT MAX(a) FROM vfoobar)

ほらね。
vfoobarのところを、SELECT foo.a, bar.b FROM foo INNER...というSELECT命令に脳内で置換してみてください。
なんだかよくわからない、複雑なクエリになってしまうことがわかる。
WITH句を使えば、こういったことを、ビューを作らないで一気にやってしまうことができる。

WITH foobar AS (
SELECT foo.a, bar.b FROM foo INNER JOIN bar ON foo.a = bar.a
)
SELECT * FROM foobar
WHERE a = (SELECT MIN(a) FROM foobar)
OR  a = (SELECT MAX(a) FROM foobar)

なるほど。やっぱりこれは便利。

WITH RECURSIVE

便利なWITH句ではあるが、本来は「再帰クエリー」というものを想定して作られたものらしい。再帰クエリーとはいったいどういったものなのであろうか。
と偉そうに書いてみたが、再帰という概念は説明するのがけっこう難しい。自分の中に自分があるような感じ。プログラミングの場合、関数の再帰呼び出し、ということをよく耳にするかと思う。関数の再帰呼び出しなら、ある関数の内部処理で、自分自身を「再帰的に」呼び出す、というもの。
よく考えずに、関数の再帰呼び出しを行うと、無限ループに陥ることになる。なぜかというと、自分が自分を呼び出して、さらに呼び出された自分も自分を呼び出すので...
通常の関数は、引数をスタックに積む。無限ループといっても、スタックが足りなくなった時点で異常終了する。また、よく考えられた再帰呼び出しは、ちゃんといつかは終了するようにして作成する。
さて、SQLに戻って再帰クエリーである。
ビューを作成する際に、SELECT命令を記述することになるのだが、そのSELECT命令に定義中のビューを使用することはできない。
なぜなら、そのビューはまだ作成されていないから。
CREATE VIEWでviewxxを作成中に、ビューのSELECT命令でviewxxを使うことはできない。

CREATE VIEW vfoo AS
SELECT * FROM vfoo

まぁ、そりゃそうだよね。再帰関数呼び出しで、無限ループに陥ってしまうことと同じ問題である。
試しに、上記のCREATE VIEWをOracleでやってみたら、エラーになった。
　SQLエラー: ORA-01731: 循環ビュー定義が検出されました
　01731. 00000 - "circular view definition encountered"
へー、「循環ビュー」っていうのか。
ビューでは、エラーになるものの、WITH句では、これが許されているのである。
マジか？無限ループにならないのか？
やってみよう。

WITH vfoo AS (
SELECT a FROM vfoo
)
SELECT * FROM vfoo
　ORA-32039: 再帰的WITH句には列別名リストが必要です
　32039. 00000 -  "recursive WITH clause must have column alias list"

えー、そうなの。めんどくさー

WITH vfoo(a) AS (
SELECT a FROM vfoo
)
SELECT * FROM vfoo
　ORA-32040: 再帰的WITH句にはUNION ALL操作を使用する必要があります
　32040. 00000 -  "recursive WITH clause must use a UNION ALL operation"

また、エラーになってしまった。
UNION ALLが必要？

WITH vfoo(a) AS (
SELECT a FROM vfoo
UNION ALL
SELECT a FROM vfoo
)
SELECT * FROM vfoo
　ORA-32043: 再帰的WITH句には初期化ブランチが必要です
　32043. 00000 -  "recursive WITH clause needs an initialization branch"

ふえーん。初期化ブランチとはいったい。
要は、再帰呼び出しで無限ループに陥らないように、ちゃんと中のクエリを定義しなさい、ということ。WITHの中で、自分自身を使っても循環ビューのエラーにはならない。
再帰クエリーにする場合は、列指定が必要。再帰クエリーの中には、自分自身を含めてよいが、UNION ALLと初期化ブランチが必要。というところまでわかった。
本日は、ここまでとする。

続き WITH 再帰クエリー

WITH 再帰クエリその２

改訂第４版SQLポケリには、WITH句、WITH再帰クエリが載ってます。
ですが、WITH RECURSIVEのRECURSIVEを書くとエラーになります、とある。これは間違いではないのだが、SQLiteでの話が抜けている。そもそもSQLiteがWITH RECURSIVEに対応していないことになっている。スミマセン。
整理すると以下のような状況である。
PostgreSQL　RECURSIVEを書く必要あり
SQLite　　　RECURSIVEを省略可能
その他　　　RECURSIVEは書いてはいけない
ということで、訂正は以下のようになる。
77ページ
【誤】
PostgreSQLの場合、再帰クエリーを実行する際には、WITHの後に
RECURSIVEをキーワードを指定する必要があります。他のデータベースでは
RECURSIVEを指定するとエラーになります。
【正】
PostgreSQLの場合、再帰クエリーを実行する際には、WITHの後に
RECURSIVEをキーワードを指定する必要があります。SQLiteでは、RECURSIVE
を省略可能です。他のデータベースではRECURSIVEを指定するとエラーになります。