SQL 派生テーブルのサポート

SELECT へ戻る

はじめに

メモ： 角括弧で囲まれた言葉は、オフィシャル SQL 標準ドキュメント内での文法生成ルールを表しています。それらの用語に関する詳しい定義については、どのドキュメントを参照してください。まず最初に使用例を示した後、それらの用語は、「角括弧なし」の形式で表記されます。

派生テーブルは、別のクエリの中で使用できる、一時的なレコードのセットです。派生テーブルは、長いクエリを短縮化し、複雑な処理を論理的なステップ群に分割することができます。派生テーブルの構文により、既存のアプリケーションの機能を拡張して、バックエンド RDBMS として InterBase を使用するようにできます。<derived table> は、<query expression> の形式内で表される <table reference>で、テーブルの行を返すよう評価されます。クエリ式は、SQL 文の中に、その派生テーブルを識別するのに使用される相関名と一緒に直接組み込むことができます。

代わりに、識別子で名前のついてクエリ式は、句の中に列挙することも可能です。その際、SQL 文および、SQL 文内で識別子によって参照される名前付きクエリより前にきます。これは、SQL 標準がオフィシャルにその述語を認知してはいませんが、非公式に「共通テーブル式」として知られています。共通テーブル式（CTE）は、SELECT、INSERT、UPDATE、DELETE の各文の中で参照される一時結果セットを返します。CTE は、複雑なサブクエリや結合をシンプルにし、コードをより読みやすく、また維持も容易にします。

また、以前の「部品表」アプリケーションを処理する再帰的クエリもサポートされており、パーツが親パーツに格納されているようなコンテナ関係に対応します。

SQL 標準では、テーブル参照によって表される文法生成ルールは、<from 句> の演算対象を表します。それまでは、InterBase SQL はテーブル参照として<joined table> と <table name> のみをサポートしていました。

用途

派生テーブルは、文レベルスコープでは「ビュー」と考えることができます。派生テーブルにより、開発者は、データベーススキーマビューを定義しなくても、ビューのような構造を使用して柔軟に表現することができます。またこれによりユーザーは、ビュー定義を作成するようデータベース管理に要求しなくても、アドホッククエリで同じ利点を享受することができます。

シンプルな例として、IB のデータベースサンプル Employee で、エントリレベルのジョブの検索について検討してみましょう。

SELECT ELJ.JOB_CODE, ELJ.JOB_TITLE FROM (SELECT JOB_CODE, JOB_TITLE FROM JOB WHERE MAX_SALARY < 50000) AS ELJ;

これは以下と同等です。

CREATE VIEW ENTRY_LEVEL_JOB AS SELECT JOB_CODE, JOB_TITLE FROM JOB WHERE MAX_SALARY < 50000;
SELECT ELJ.JOB_CODE, ELJ.JOB_TITLE FROM ENTRY_LEVEL_JOB AS ELJ;

また、<with clause> を使用すると、派生テーブルが文の中でアンドも参照される場合、または再帰的クエリの場合、SQL 文のプロパティの前に一連の名前付きクエリを列挙することもできます。

WITH ELJ AS (SELECT JOB_CODE, JOB_TITLE FROM JOB WHERE MAX_SALARY < 50000)
      SELECT JOB_CODE, JOB_TITLE FROM ELJ;

次の、派生テーブルの SQL 構文は、SQL 標準からのものです。InterBase は <correlation name> のための <derived column list> 句をサポートしていない点に注意してください。このため、派生テーブルサポートにおけるこの機能は、本リリースでは失われています。

<table reference> ::=
         <table factor>
       | <joined table>
<table factor> ::=
        <table primary> [ <sample clause> ]
<table primary> ::=
       <table or query name> [ <query system time period specification> ]
               [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ]
     | <derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ]
<derived table> ::=
       <table subquery>
<table subquery> ::=
       <subquery>
<subquery> ::=
       <left paren> <query expression> <right paren>

WITH 句の次の SQL 構文は、SQL 標準からのものです。InterBase はクエリ式の <order by clause>、<result offset clause>、<fetch first clause> をサポートしていない点に注意してください。文レベルでのみ、同等の構文をサポートしています。

<query expression> ::=
       [ <with clause> ] <query expression body>
             [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
<with clause> ::=
       WITH [ RECURSIVE ] <with list>
<with list> ::=
       <with list element> [ { <comma> <with list element> }... ]
<with list element> ::=
       <query name> [ <left paren> <with column list> <right paren> ]
              AS <table subquery>
<with column list> ::=
       <column name list>
<query expression body> ::=
       <query term>
     | <query expression body> UNION [ ALL | DISTINCT ]
           [ <corresponding spec> ] <query term>
<query term> ::=
       <query primary>
<query primary> ::=
        <simple table>
      | <left paren> <query expression body>
            [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
        <right paren>
<simple table> ::=
       <query specification>

要件と制約

ユーザーは、派生テーブルによってアクセスされる基底テーブルやビューに対して、適切なアクセス権限を持っていなければなりません。
派生テーブル構文は、ユーザーアプリケーションだけでなく、トリガおよびストアドプロシージャでも使用できます。
動的 SQL は、派生テーブル構文をサポートしています。
対話型 SQL (isql) はは静テーブル構文をサポートしています。
組み込み SQL (static) は、派生テーブル構文をサポートしていません。

移行に関わる問題 派生テーブル構文を使用する SQL 文は、"2020 Update 6" が稼働する InterBase サーバーまたはモバイルエディション、およびそれに続くエディション上でのみ、実行可能です。既存のクライアントは、InterBase DSQL 構文を使用して、派生テーブル構文がある SQL 文を投稿することができます。InterClient などを含む、DB フレームワークをサポートするサードパーティでは、そのような文を問題なく渡すことができています。