logo

TechCodeview

SQL Server の共通テーブル式 (CTE)

SQL Server の共通テーブル式 (CTE) を使用して、複雑な結合とサブクエリを簡単にします。また、組織階層などの階層データをクエリする方法も提供します。この記事では、CTE の完全な概要、CTE の種類、利点、欠点、および SQL Server での使用方法について説明します。

SQL Server の CTE とは何ですか?

CTE (共通テーブル式) は、クエリの実行中にのみ存在する 1 回限りの結果セットです。 。これにより、単一の SELECT、INSERT、UPDATE、DELETE、CREATE VIEW、または MERGE ステートメントの実行スコープ内のデータを参照できるようになります。結果はどこにも保存できず、クエリの実行が完了するとすぐに失われるため、これは一時的なものです。最初は SQL Server 2005 バージョンに付属していました。 DBA は常に、サブクエリ/ビューの代替として CTE を使用することを好みました。これらは ANSI SQL 99 標準に従っており、SQL に準拠しています。

SQL Server の CTE 構文

CTE 構文には、CTE 名、オプションの列リスト、および共通テーブル式 (CTE) を定義するステートメント/クエリが含まれます。 CTE を定義すると、それを SELECT、INSERT、UPDATE、DELETE、および MERGE クエリのビューとして使用できます。

SQL Server の CTE の基本構文は次のとおりです。

 WITH cte_name (column_names) AS (query) SELECT * FROM cte_name;

この構文では次のようになります。

  • 最初に CTE 名を指定しました。これは後でクエリで参照されます。
  • 次のステップでは、カンマ区切りの列のリストを作成します。これにより、CTE 定義引数内の列の数とクエリ内の列の数が同じであることが保証されます。 CTE 引数の列を定義していない場合は、CTE を定義するクエリ列が使用されます。
  • その後、式名の後に AS キーワードを使用し、結果セットが CTE に設定される SELECT ステートメントを定義します。
  • 最後に、SELECT、INSERT、UPDATE、DELETE、MERGE ステートメントなどのクエリで CTE 名を使用します。

CTE クエリ定義を作成する際には、次の点に留意する必要があります。次の条項は使用できません。

  1. TOP 句としても使用する場合を除き、ORDER BY
  2. の中へ
  3. クエリヒントを含む OPTION 句
  4. 閲覧用

以下の画像は、CTE クエリ定義を表したものです。

SQL Server の CTE

ここで、最初の部分は、SQL で独立して実行できる SQL クエリを含む CTE 式です。 2 番目の部分は、CTE を使用して結果を表示するクエリです。

例

さまざまな例を使用して、SQL Server で CTE がどのように機能するかを理解しましょう。ここではテーブルを使用します。 お客様 」というデモンストレーションを行いました。このテーブルに次のデータが含まれているとします。

SQL Server の CTE

この例では、CTE 名は次のとおりです。 ニューヨークの顧客 、CTE を定義するサブクエリは 3 つの列を返します。 お客様のお名前、メールアドレス、 そして 州 。その結果、CTE customer_in_newyork はニューヨーク州に住むすべての顧客を返します。

CTE customer_in_newyork を定義した後、それを 選択する ステートメントを使用して、ニューヨーク在住の顧客の詳細を取得します。

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York') SELECT c_name, email, state FROM customers_in_NewYork;

上記のステートメントを実行すると、次の出力が得られます。ここでは、結果としてニューヨーク州に居住する顧客情報のみが返されることがわかります。

SQL Server の CTE

複数の CTE

場合によっては、結果を確認するために複数の CTE クエリを作成し、それらを結合する必要があります。このシナリオでは複数の CTE の概念を使用する場合があります。カンマ演算子を使用して複数の CTE クエリを作成し、それらを 1 つのステートメントにマージする必要があります。複数の CTE を区別するには、カンマ演算子「,」の前に CTE 名を付ける必要があります。

複数の CTE は、最終的に結合される複雑なクエリを簡素化するのに役立ちます。それぞれの複雑な部分には独自の CTE があり、WITH 句の外側で参照したり結合したりできます。

注: 複数の CTE 定義は、UNION、UNION ALL、JOIN、INTERSECT、または EXCEPT を使用して定義できます。

以下の構文でより明確に説明されています。 

 WITH cte_name1 (column_names) AS (query), cte_name2 (column_names) AS (query) SELECT * FROM cte_name UNION ALL SELECT * FROM cte_name;

例

SQL Server で複数の CTE がどのように機能するかを理解しましょう。ここでは、上記の ' を使用します。 お客様 ' デモンストレーション用のテーブル。

この例では、2 つの CTE 名を定義しました。 ニューヨークの顧客 そして カリフォルニアの顧客 。次に、これらの CTE のサブクエリの結果セットが CTE に設定されます。最後に、CTE 名をクエリで使用し、次の場所にいるすべての顧客を返します。 ニューヨーク そして カリフォルニア州 。

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York'), customers_in_California AS (SELECT * FROM customer WHERE state = 'California') SELECT c_name, email, state FROM customers_in_NewYork UNION ALL SELECT c_name, email, state FROM customers_in_California;

ニューヨーク州とカリフォルニア州。

SQL Server の CTE

なぜ CTE が必要なのでしょうか?

データベース ビューや派生テーブルと同様に、CTE は複雑なクエリを読みやすくシンプルにすることで、複雑なクエリの作成と管理を容易にします。この特性は、複雑なクエリをクエリの書き換えで再利用できる単純なブロックに分割することで実現できます。

その使用例の一部を以下に示します。

  • これは、単一のクエリ内で派生テーブルを複数回定義する必要がある場合に便利です。
  • これは、データベース内にビューの代替を作成する必要がある場合に便利です。
  • これは、複数のクエリ コンポーネントに対して同じ計算を複数回同時に実行する必要がある場合に便利です。
  • ROW_NUMBER()、RANK()、NTILE() などのランキング関数を使用する必要がある場合に便利です。

その利点の一部を以下に示します。

DFS vs BFS
  • CTE により、コードのメンテナンスが容易になります。
  • CTE により、コードの可読性が向上します。
  • これにより、クエリのパフォーマンスが向上します。
  • CTE を使用すると、再帰クエリを簡単に実装できます。

SQL Server の CTE の種類

SQL Server は、CTE (共通テーブル式) を次の 2 つの大きなカテゴリに分類します。

  1. 再帰的 CTE
  2. 非再帰的 CTE

再帰的 CTE

共通テーブル式は、それ自体を参照する再帰的 CTE として知られています。その概念は再帰に基づいており、「」として定義されます。 再帰的なプロセスまたは定義を繰り返し適用すること 。」再帰クエリを実行すると、データのサブセットに対して繰り返し反復処理が行われます。これは、それ自体を呼び出すクエリとして単純に定義されます。ある時点で終了条件があるため、無限に呼び出しを行うわけではありません。

再帰的 CTE には、 すべてを結合する ステートメントと、再帰的に CTE 自体を参照する 2 番目のクエリ定義です。

例

SQL Server で再帰的 CTE がどのように機能するかを理解しましょう。以下のステートメントを考えてみましょう。 一連の最初の 5 つの奇数を生成します。 

 WITH odd_num_cte (id, n) AS ( SELECT 1, 1 UNION ALL SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>When we execute this recursive CTE, we will see the output as below:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-5.webp" alt="CTE in SQL Server"> <p>The below example is the more advanced recursive CTE. Here, we are going to use the &apos; <strong>jtp_employees</strong> &apos; table for a demonstration that contains the below data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-6.webp" alt="CTE in SQL Server"> <p>This example will display the hierarchy of employee data. Here table provides a reference to that person&apos;s manager for each employee. The reference is itself an employee id within the same table.</p> <pre> WITH cte_recursion (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 1 FROM jtp_employees WHERE ManagerID IS NULL UNION ALL SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.ManagerID, r.EmpLevel + 1 FROM jtp_employees emp INNER JOIN cte_recursion r ON emp.ManagerID = r.EmpID ) SELECT FirstName + &apos; &apos; + LastName AS FullName, EmpLevel, (SELECT FirstName + &apos; &apos; + LastName FROM jtp_employees WHERE EmployeeID = cte_recursion.MgrID) AS Manager FROM cte_recursion ORDER BY EmpLevel, MgrID </pre> <p>This CTE will give the following output where we can see the hierarchy of employee data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-7.webp" alt="CTE in SQL Server"> <h3>Non-Recursive CTE</h3> <p>A common table expression that doesn&apos;t reference itself is known as a non-recursive CTE. A non-recursive CTE is simple and easier to understand because it does not use the concept of recursion. According to the CTE Syntax, each CTE query will begin with a &apos; <strong>With</strong> &apos; clause followed by the CTE name and column list, then AS with parenthesis.</p> <h2>Disadvantages of CTE</h2> <p>The following are the limitations of using CTE in SQL Server:</p> <ul> <li>CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.</li> <li>The CTE can only be referenced once by the Recursive member.</li> <li>We cannot use the table variables and CTEs as parameters in stored procedures.</li> <li>We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.</li> <li>Since it&apos;s just a shortcut for a query or subquery, it can&apos;t be reused in another query.</li> <li>The number of columns in the CTE arguments and the number of columns in the query must be the same.</li> </ul> <hr></5>

この CTE は、従業員データの階層を確認できる次の出力を提供します。

SQL Server の CTE

非再帰的 CTE

それ自体を参照しない共通テーブル式は、非再帰 CTE として知られています。非再帰 CTE は再帰の概念を使用しないため、シンプルで理解しやすくなります。 CTE 構文に従って、各 CTE クエリは ' で始まります。 と ' 句の後に CTE 名と列リストが続き、次に括弧付きの AS が続きます。

CTEの欠点

SQL Server で CTE を使用する場合の制限は次のとおりです。

  • CTE メンバーは、Distinct、Group By、Having、Top、Joins などのキーワード句を使用できません。
  • CTE は、Recursive メンバーによって 1 回だけ参照できます。
  • テーブル変数と CTE をストアド プロシージャのパラメータとして使用することはできません。
  • CTE をビューの代わりに使用できることはすでにわかっていますが、CTE はネストできませんが、ビューはネストできます。
  • これはクエリまたはサブクエリの単なるショートカットであるため、別のクエリで再利用することはできません。
  • CTE 引数の列の数とクエリの列の数は同じである必要があります。