[SQLの基本]一時テーブルとCTE（WITH句）入門: 効率的なデータ操作のためのツール

はじめに

SQLを使用してデータベースを操作する際、一時的にデータを保存して複雑なクエリを簡潔にする方法がいくつかあります。その中でも「一時テーブル（Temporary Table）」と「CTE（Common Table Expressions、WITH句）」は非常に便利なツールです。今回は、一時テーブルとCTEの基本とその使用方法について解説します。

一時テーブル（Temporary Table）

一時テーブルは、セッションごとに一時的に作成されるテーブルであり、セッションが終了すると自動的に削除されます。一時テーブルを使用することで、複雑なクエリを簡潔にし、パフォーマンスを向上させることができます。

一時テーブルの作成

一時テーブルを作成するには、CREATE TEMPORARY TABLE文を使用します。

CREATE TEMPORARY TABLE temp_table_name (
  column1 datatype,
  column2 datatype,
    ...
);

例 従業員の一時テーブルを作成するクエリは次の通りです。

CREATE TEMPORARY TABLE temp_employees (
  employee_id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  salary DECIMAL(10, 2)
);

一時テーブルへのデータ挿入

一時テーブルにデータを挿入するには、通常のINSERT文を使用します。

INSERT INTO temp_employees (employee_id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 50000.00),(2, 'Jane', 'Smith', 60000.00);

一時テーブルの使用

一時テーブルを使用してデータを操作するには、通常のテーブルと同様にクエリを実行します。

SELECT 
  * 
FROM temp_employees;

一時テーブルの削除

一時テーブルはセッションが終了すると自動的に削除されますが、手動で削除することもできます。

DROP TEMPORARY TABLE temp_employees;

CTE（Common Table Expressions、WITH句）

CTEは、一時的な結果セットを定義し、それを元にクエリを実行するための構文です。CTEを使用することで、クエリを分かりやすくし、再利用可能なクエリ部品を作成できます。

CTEの作成

CTEを作成するには、WITH句を使用します。

WITH cte_name AS (
  SELECT
    column1, 
    column2, ...
  FROM table_name
  WHERE condition
)
SELECT 
  column1, 
  column2, ...
FROM cte_name;

例 従業員テーブルから給与が50000以上の従業員を取得するCTEを作成するクエリは次の通りです。

WITH HighSalaryEmployees AS (
  SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
  FROM employees
  WHERE salary >= 50000
)
SELECT 
  * 
FROM HighSalaryEmployees;

このクエリは、給与が50000以上の従業員の情報を含む一時的な結果セットを作成し、それを元にデータを取得します。

再帰CTE

再帰CTEは、自分自身を再帰的に参照することで階層的なデータを操作するために使用されます。

WITH RECURSIVE cte_name AS (
  -- 基底部
  SELECT 
    column1, 
    column2, ...
  FROM table_name
  WHERE condition
  UNION ALL
  -- 再帰部
  SELECT 
    column1, 
    column2, ...
  FROM table_name
  JOIN cte_name ON condition
)
SELECT 
  *
FROM cte_name;

例 組織内の階層構造を再帰的に取得するクエリは次の通りです。

WITH RECURSIVE EmployeeHierarchy AS (
    -- 基底部
    SELECT employee_id, first_name, last_name, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 再帰部
    SELECT e.employee_id, e.first_name, e.last_name, e.manager_id
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

このクエリは、従業員の階層構造を再帰的に取得します。

一時テーブルとCTEの使い分け

一時テーブルとCTEはどちらも便利なツールですが、使用する場面が異なります。

一時テーブル: 複数のクエリで同じデータを何度も使用する場合や、大量のデータを一時的に保存する場合に適しています。

CTE: クエリを分かりやすくし、再利用可能なクエリ部品を作成する場合に適しています。CTEは一時的な結果セットを定義し、その結果を元にクエリを実行するため、クエリの可読性を向上させます。