Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

WITH 句 / 共通テーブル式 (Common Table Expression : SQL-99)

WITH 句は、副問い合わせに名前を付ける句である。この名前を付けられた副問い合わせは、「インラインビュー」 または 「一時表」として使用される。
同じインラインビューを何度も SQL の中に記述するよりも WITH 句で問い合わせに名前付けを行なうことで視認性が高まり、場合によりパフォーマンスが向上する可能性がある。

特に WITH では一時表を作ることがあるという特殊性(※)から実行計画に Merge Join Cartesian（直積）が出てビックリすることがあるかもしれない。

しかし、これは「大きな表と小さな表の結合」＋「大きな表と別な小さな表の結合」の検索処理より
「小さな表同士の直積結合」＋「直積した表と大きな表の結合」を選択した方が高速であるとオプティマイザが算出した結果なので涼しい顔で対応しよう。

(※) メリットがある一方で初期リリースではバグに遭遇する可能性はある。

WITH 句 の構文

構文 〜

WITH
  query_name AS ( select col [, col2 …] 〜 )

構文 カラムの別名と再帰がサポートされている。

WITH
  query_name [ ( col1_alias [,col2_alias …] ) ] AS ( select col [, col2 …] 〜 )
  [
    SEARCH { DEPTH | BREADTH }  FIRST BY
           col1_alias [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, col2_alias …]
     SET ordering_col
  ] [
    CYCLE col1_alias [,col2_alias …]
     SET cycle_mark_c_alias TO cycle_value DEFAULT no_cycle_value
  ]

DEPTH : 深さ優先探索 | BREADTH : 幅優先探索　
cycle_value : サイクルしているときの値 | no_cycle_value : サイクルしていないときの値

WITH と インラインビュー

同じインラインビューを同一 SQL 内で多用した場合と WITH 句を使用した場合の違いは WITH 句では、その結果を一時表として保存される可能性がある点である。
言い換えると WITH の結果セットをもつワークテープルがあるかのように振舞う場合がありインラインビューよりパフォーマンスが良くなる可能性がある。

WITH 使用例

1. one という名前の副問い合わせを select 1 from dual で作る。
2. ones_next という名前の副問い合わせを one の問い合わせを使って作る。(※)

(※) one と ones_next の記述順序は逆にできず 「ORA-32031: WITH句の問合せ名の参照が無効です。」 となる。

主問い合わせで ones_next を参照する

SQL> WITH
  2    one AS (
  3      select 1 one_col1, 10 one_col2 from dual
  4    ),
  5    ones_next AS (
  6      select one_col1 + 1 from one
  7    )
  8  select * from ones_next;
 
ONE_COL1+1
----------
         2

簡単な再帰例 以降

ones_next が 自分自身 ones_next を再帰的に参照する例

SQL> WITH
  2    ones_next (col) AS (
  3      select 1 from dual
  4      union all
  5      select 1 + col from ones_next where col < 5
  6    )
  7  select * from ones_next;
 
       COL
----------
         1
         2
         3
         4
         5

Oracle 10g では 「ORA-32033: 列はサポートされていません。」 とエラーになる。

副問い合わせ 関連

• EXISTS ( 副問い合わせ )
• IN と EXISTS 2つの副問い合わせの違い