WITH修飾子の非再帰的な名前付きクエリー - Teradata Database - Teradata Vantage NewSQL Engine - 非再帰的な名前付きクエリー、WITH修飾子

次のテーブル定義があるとします。

     CREATE TABLE product (
       product_id INTEGER,
       on_hand    INTEGER);

    CREATE TABLE stocked (
      store_id   INTEGER,
      product_id INTEGER,
      quantity   INTEGER);

以下の文では、非再帰的WITH文修飾子を使用することによって、ASキーワードの後の選択式から生成される、orderable_itemsという名前付き一時結果セットを定義しています。

     WITH orderable_items (product_id, quantity) AS
     ( SELECT stocked.product_id, stocked.quantity
       FROM stocked, product
       WHERE stocked.product_id = product.product_id
       AND   product.on_hand > 5
    )
     
SELECT product_id, quantity
     FROM orderable_items
     WHERE quantity < 10;

派生テーブルを使用した次の文も、これと同じ結果になります。

     SELECT product_id, quantity
     FROM (SELECT stocked.product_id, stocked.quantity
           FROM stocked, product
           WHERE stocked.product_id = product.product_id
           AND   product.on_hand > 5) AS orderable_items
     WHERE quantity < 10;

WITH修飾子の再帰的な名前付きクエリー

再帰的な名前付きクエリーは、組織構造、部品テーブル、文書階層などの、データの階層を反復的に問合わせる方法です。

再帰的問合わせは、別の再帰的問合わせを直接的にも間接的にも参照できません。

再帰的な名前付きクエリーには、次の3つの実行フェーズがあります。

1. 初期非再帰的またはシード結果セットを作成します。
2. 一時的な名前の付いた結果セットに追加される行がなくなるまで、シードの結果セットに基づいて中間結果セットを再帰します。
3. 一時的な名前の付いた結果セットについて最後の問合わせを実行し、最終の結果セットを返します。

次のemployeeテーブルについて考えてみましょう。

     CREATE TABLE employee (
       employee_number         INTEGER,
       manager_employee_number INTEGER,
       last_name               CHARACTER(20),
       first_name              VARCHAR(30));

テーブルは、employeeとmanagerの関係の組織的な構造を表わします。employeeテーブルは、この組織図に似ています。

以下の再帰的問合わせは、employee_number値が801の社員を直接的または間接的に上司とする社員すべての社員番号を取り出すためのものです。

     WITH RECURSIVE temp_table (employee_number) AS
       (SELECT root.employee_number
        FROM employee AS root
        WHERE root.manager_employee_number = 801
     UNION ALL
        SELECT indirect.employee_number
        FROM temp_table AS direct, employee AS indirect
        WHERE direct.employee_number = indirect.manager_employee_number
       )
     SELECT *
     FROM temp_table
     ORDER BY employee_number;

この例のtemp_tableは、再帰文のFROM句の中で参照可能な名前付き一時結果セットです。

非再帰文またはシード文によってtemp_tableの中に初期結果セットが生成され、employee_numberが801の社員を直接的に上司とする社員がそこに入れられます。

     SELECT root.employee_number
     FROM employee AS root
     WHERE root.manager_employee_number = 801

temp_tableに含まれる各社員と、temp_tableに含まれる社員を上司とする社員を結合することにより、再帰処理が実行されます。UNION ALLによりその結果がtemp_tableに追加されます。

     SELECT indirect.employee_number
     FROM temp_table AS direct, employee AS indirect
     WHERE direct.employee_number = indirect.manager_employee_number

新しい行がtemp_tableに追加されなくなった時点で再帰処理が停止します。

最終クエリーは再帰的WITHリクエスト修飾子の一部ではありませんが、そこでtemp_tableから社員情報を抽出しています。

     SELECT *
     FROM temp_table
     ORDER BY employee_number;

この再帰問合わせの結果は次のとおりです。

  employee_number
  ---------------
             1001
             1002
             1003
             1004
             1006
             1008
             1010
             1011
             1012
             1014
             1015
             1016
             1019

WITH修飾子