プログラミング

SQLの共通テーブル式（CTE）解説

最終更新： 26/03/2025
1 分の読み

SQLにおける共通テーブル式（CTE）完全ガイド

SQL（Structured Query Language）は、データベースから情報を操作するために使用される標準的な言語です。SQLの中でも、「共通テーブル式（CTE）」は非常に重要で強力な機能の一つです。本記事では、CTEの基本的な使い方から、実践的な応用方法までを完全かつ包括的に解説します。

関連記事

1. 共通テーブル式（CTE）の基本

共通テーブル式（CTE）は、SQLクエリの中で一時的に名前を付けた結果セットを定義する方法です。この結果セットは、クエリ内で後から参照することができます。CTEは主に、複雑なクエリを簡潔に記述したり、再利用可能なサブクエリを作成するために使用されます。

CTEは、WITHキーワードを使って定義します。基本的な構文は次の通りです。

sql
WITH cte_name AS (
    -- サブクエリの内容
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name;

上記の例では、cte_nameという名前でCTEを定義し、その後SELECT文でCTEを参照しています。CTEは、一度定義するとその後のクエリで何度でも使用できます。

2. CTEの使用例

2.1. 単純なCTE

最も基本的なCTEの使用例を見てみましょう。例えば、従業員テーブルから特定の部署に属する従業員の名前を取得する場合、以下のようなクエリを書きます。

sql
WITH dept_employees AS (
    SELECT employee_name
    FROM employees
    WHERE department = 'Sales'
)
SELECT * FROM dept_employees;

この例では、まずdept_employeesというCTEを定義し、Sales部署の従業員を取得しています。その後、CTEからデータを選択しています。

2.2. 再帰的CTE

再帰的CTEは、自己参照を使ったクエリを作成する際に使用されます。特に、階層的なデータ（例えば、組織の階層やフォルダ構造）を扱う場合に役立ちます。

再帰的CTEの構文は少し特殊です。基本的な例を見てみましょう。以下の例では、社員の管理者関係を示すデータを元に、再帰的CTEを使って上司を辿っていきます。

sql
WITH RECURSIVE EmployeeHierarchy AS (
    -- 基本部分: 最上層の社員（例: CEO）
    SELECT employee_id, employee_name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 再帰部分: 管理者を辿る
    SELECT e.employee_id, e.employee_name, e.manager_id
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

このクエリでは、最上層の管理者（manager_id IS NULL）からスタートし、再帰的に全ての管理者を辿っています。UNION ALLを使って再帰部分と基本部分を結合しています。

3. CTEの利点

CTEを使うことによる主な利点は以下の通りです。

3.1. クエリの可読性の向上

CTEは、複雑なサブクエリを明示的に定義できるため、SQLクエリの可読性が向上します。サブクエリが多くなると、クエリが非常に長くなり、理解が難しくなりますが、CTEを使うことで、個々の部分を名前付きで定義できるため、全体の構造がわかりやすくなります。

3.2. クエリの再利用性

CTEは、クエリ内で何度でも参照することができます。これにより、同じデータを繰り返し取得する必要がある場合、CTEを使って効率的にコードを再利用することができます。

3.3. 複雑な集計の処理

CTEを使うことで、複雑な集計や結合処理を段階的に行い、クエリを簡素化できます。特に、JOINやGROUP BYを多用する場合に効果的です。

4. CTEの応用

4.1. 複数のCTEを使う

CTEは複数定義することができます。これにより、複数の一時的な結果セットを段階的に処理することが可能になります。

sql
WITH first_cte AS (
    SELECT column1, column2
    FROM table1
),
second_cte AS (
    SELECT column1, column2
    FROM table2
)
SELECT * 
FROM first_cte
JOIN second_cte ON first_cte.column1 = second_cte.column1;

この例では、first_cteとsecond_cteという2つのCTEを定義し、それらを結合しています。

4.2. 集計結果をCTEで処理する

集計関数をCTEと組み合わせることで、複雑な集計を分かりやすく処理することができます。

sql
WITH sales_summary AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY product_id
)
SELECT p.product_name, ss.total_sales
FROM products p
JOIN sales_summary ss ON p.product_id = ss.product_id;

このクエリでは、salesテーブルから商品の売上合計をCTEで計算し、それをproductsテーブルと結合して表示しています。

5. CTEとサブクエリの違い

CTEとサブクエリは似ている部分もありますが、いくつかの重要な違いがあります。

  • 可読性: CTEは名前を付けて定義するため、長いクエリでも可読性が高くなります。一方、サブクエリはその場で結果を返すため、長くなると理解しにくくなる場合があります。

  • 再利用性: CTEは同じクエリ内で何度も再利用できますが、サブクエリはその場限りの使用にとどまります。

  • パフォーマンス: パフォーマンスについては、CTEとサブクエリで大きな違いが生じることは少ないですが、特定の状況では最適化に差が出ることもあります。

6. 結論

共通テーブル式（CTE）は、SQLにおける強力なツールであり、複雑なクエリをシンプルで可読性の高いものに変えることができます。再帰的CTEを使うことで、階層的なデータを簡単に扱うことができ、複数のCTEを組み合わせることで、より効率的で柔軟なクエリ作成が可能となります。CTEを効果的に活用することで、SQLの可読性とパフォーマンスを向上させることができるため、積極的に活用していきましょう。

最終更新： 26/03/2025
1 分の読み
Back to top button