MySQL & MariaDBの世界

MySQL WITH句を徹底解説｜初心者から実務で使える応用例まで

MySQL
佐川 直弘 | Naohiro Sagawa
By 佐川 直弘 | Naohiro Sagawa (運営者)
※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　
目次

1. はじめに

MySQLは多くの開発者やデータベース管理者に利用されているデータベース管理システムで、強力かつ柔軟なSQL機能を提供します。その中でも、MySQL 8.0で導入されたWITH句（共通テーブル式、Common Table Expression: CTE）は、SQLクエリをより読みやすくし、保守性を向上させるための強力なツールです。

本記事では、このWITH句の基本から応用までを初心者から中級者までを対象に詳しく解説します。特に、サブクエリの置き換えや再帰的なクエリの実装といった実用的な内容を取り上げます。

SQLを学んでいる方や日々の業務でクエリの効率化に悩んでいる方にとって、この記事が具体的な解決策となることを目指しています。以下の内容に沿って、WITH句の基本を理解し、実務に役立ててください。

2. WITH句（共通テーブル式）の基礎知識

WITH句とは何か？

WITH句は、SQLクエリ内で一時的な結果セット（共通テーブル式、CTE）を定義し、それを後続のクエリで利用するための構文です。MySQL 8.0からサポートされており、複雑なサブクエリを簡潔でわかりやすい形に置き換えることができます。

例えば、サブクエリをそのまま書く場合、可読性が低下し、クエリ全体が長くなりがちです。WITH句を使えば、クエリを論理的なブロックに分けることができ、理解しやすくなります。

WITH句の基本構文

以下は、WITH句の基本的な構文です。

WITH テーブル名 AS (
  SELECT 列1, 列2
  FROM 元のテーブル
  WHERE 条件
)
SELECT 列1, 列2
FROM テーブル名;

この構文では、WITHの後に仮想的なテーブル（共通テーブル式）を定義し、それをメインクエリで利用します。これにより、繰り返し使用されるサブクエリを簡潔に表現できます。

サブクエリやビューとの違い

WITH句は一時的に利用可能な結果セットを作成するもので、サブクエリやビューといくつかの点で異なります。

特徴WITH句サブクエリビュー
スコープクエリ内のみ有効定義された場所でのみ使用可能データベース全体で再利用可能
一時性一時的一時的永続的
用途複雑なクエリを簡素化する一時的なデータ抽出頻繁に再利用するデータ抽出

WITH句は、サブクエリよりも可読性が高く、ビューのように永続的なオブジェクトを作成する必要がない場合に最適です。

WITH句を使用するメリット

  1. クエリの可読性向上
    サブクエリが複数ある場合でも、WITH句で整理することで構造が明確になります。
  2. 再利用性の向上
    一時的な結果セットを定義することで、クエリ内で何度も参照できます。
  3. 保守性の向上
    クエリを論理的に分割できるため、修正や拡張が容易になります。

3. MySQL WITH句の基本的な使い方

サブクエリの置き換え

WITH句は、複雑なサブクエリを簡素化するための強力なツールです。サブクエリを直接埋め込むと、クエリ全体が複雑になり、読みづらくなることがありますが、WITH句を使うことで可読性を向上させることができます。

以下は、WITH句を使ってサブクエリを置き換える基本的な例です。

サブクエリを使った場合:

SELECT AVG(sales.total) AS average_sales
FROM (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) AS sales;

WITH句を使った場合:

WITH sales AS (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT AVG(sales.total) AS average_sales
FROM sales;

この例では、salesという一時的な結果セットをWITH句で定義し、それをメインクエリで利用しています。これにより、クエリ全体が分かりやすく整理されます。

複数の共通テーブル式（CTE）の定義

WITH句では、複数のCTEを定義することが可能です。これにより、複雑なクエリをさらにモジュール化できます。

例:

WITH 
  sales_per_customer AS (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ),
  high_value_customers AS (
    SELECT customer_id
    FROM sales_per_customer
    WHERE total_sales > 10000
  )
SELECT customer_id
FROM high_value_customers;

この例では、sales_per_customerで各顧客の売上合計を計算し、それを基にhigh_value_customersで高額購入顧客を抽出しています。複数のCTEを順番に利用することで、クエリを段階的に構築できます。

ネストされたCTEの利用方法

ネストされたCTEを使用することで、さらに複雑なデータ操作を行うことができます。

例:

WITH 
  sales_data AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY product_id
  ),
  ranked_sales AS (
    SELECT product_id, total_sales,
           RANK() OVER (ORDER BY total_sales DESC) AS rank
    FROM sales_data
  )
SELECT product_id, total_sales
FROM ranked_sales
WHERE rank <= 5;

このクエリでは、sales_dataで各商品の売上を集計し、ranked_salesで売上の順位付けを行っています。そして、最終的にトップ5の商品を抽出します。

実務での基本的な使い方のポイント

  1. クエリを分割して考える
    CTEを段階的に構築することで、クエリを読みやすくし、デバッグを容易にします。
  2. 一時的な計算結果の保存
    複数回使用する計算結果やフィルタリング条件をCTEにまとめることで、コードの重複を削減できます。
  3. 大規模データでの注意
    CTEは一時的な結果セットを生成するため、大量のデータを扱う場合はパフォーマンスへの影響を考慮する必要があります。

4. 再帰的なWITH句の応用例

再帰的なWITH句とは？

再帰的なWITH句（再帰CTE）は、共通テーブル式を用いて自己参照的にクエリを繰り返し実行し、階層データや繰り返し計算を処理する方法です。再帰CTEは、MySQL 8.0以降でサポートされており、親子関係や階層構造を持つデータを扱う際に特に便利です。

再帰CTEの基本構文

再帰CTEを定義する際には、WITH RECURSIVEキーワードを使用します。基本構文は以下のとおりです：

WITH RECURSIVE 再帰テーブル名 AS (
  初期クエリ -- 再帰の開始点
  UNION ALL
  再帰クエリ -- 再帰的に呼び出されるクエリ
)
SELECT * FROM 再帰テーブル名;
  • 初期クエリ: 再帰処理の最初のデータセットを取得します。
  • 再帰クエリ: 初期クエリまたは前回の結果を基に新しいデータを生成します。
  • UNION ALL: 初期クエリと再帰クエリの結果を結合します。

階層データを処理する例

再帰CTEは、階層構造を持つデータ（例: 組織ツリーやカテゴリツリー）を展開する際に活用されます。

例: 社員の管理階層を展開する

以下のemployeesテーブルを例に考えます：

employee_idnamemanager_id
1AliceNULL
2Bob1
3Charlie1
4David2

このデータを使って、ある社員を起点にした全階層を取得するクエリを作成します。

WITH RECURSIVE employee_hierarchy AS (
  -- 初期クエリ: トップレベルの社員を取得
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 再帰クエリ: 直属の部下を取得
  SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

結果:

employee_idnamemanager_idlevel
1AliceNULL1
2Bob12
3Charlie12
4David23

このクエリでは、再帰的にmanager_idを基に部下を検索し、階層全体を展開しています。

再帰CTEの制限と注意点

  1. 再帰の終了条件が必要
    再帰クエリが終了条件を満たさない場合、無限ループが発生する可能性があります。適切な条件を設けて無限ループを防ぎましょう。
  2. パフォーマンスへの影響
    再帰CTEは大量のデータに対して多くの計算を伴うため、クエリの実行時間が長くなる場合があります。LIMIT句やフィルタリング条件を活用して効率化を図りましょう。
  3. 再帰深度の制限
    MySQLでは、再帰の深度に制限があり、非常に深い再帰処理を行う場合には注意が必要です。この制限はmax_recursive_iterationsパラメータで設定可能です。

再帰CTEを活用するシナリオ

  • フォルダ構造のトラバース: フォルダやサブフォルダを再帰的に検索する。
  • 組織図の作成: 上司から部下への階層を視覚化。
  • カテゴリツリーの表示: 商品カテゴリやタグの階層を取得。

再帰CTEは、これらのシナリオでSQLクエリを簡潔に記述し、可読性を向上させる強力な手段です。

5. WITH句の使用時の注意点

パフォーマンスへの影響と最適化

  1. CTEの再計算
    WITH句で定義されたCTEは、基本的にクエリが参照されるたびに再計算されます。したがって、同じCTEを複数回利用すると、クエリの実行時間が長くなる可能性があります。 例:
   WITH sales AS (
     SELECT product_id, SUM(amount) AS total_sales
     FROM orders
     GROUP BY product_id
   )
   SELECT * FROM sales WHERE total_sales > 1000;
   SELECT COUNT(*) FROM sales;

上記の場合、salesが2回参照されるため、2回計算が行われます。これを防ぐには、複数回参照する場合には一時テーブルに結果を保存する方法が有効です。

解決策:

   CREATE TEMPORARY TABLE temp_sales AS
   SELECT product_id, SUM(amount) AS total_sales
   FROM orders
   GROUP BY product_id;

   SELECT * FROM temp_sales WHERE total_sales > 1000;
   SELECT COUNT(*) FROM temp_sales;
  1. 複雑なCTEの分割
    WITH句がネストされすぎると、クエリ全体が複雑化し、デバッグが難しくなる場合があります。一つのCTE内での処理が過剰に複雑にならないように、適切に分割することが重要です。

大量データでの使用

WITH句は、実行中に一時的なデータセットを生成します。大量のデータを扱う場合、これがメモリやストレージに負担をかけることがあります。

対策方法:

  • WHERE句でデータを絞る
    CTE内で不要なデータをフィルタリングすることで、計算量を削減できます。
  WITH filtered_orders AS (
    SELECT *
    FROM orders
    WHERE order_date > '2023-01-01'
  )
  SELECT customer_id, SUM(amount)
  FROM filtered_orders
  GROUP BY customer_id;
  • LIMIT句の活用
    データ量が多い場合は、必要なデータのみを抽出するためにLIMIT句を使用します。

MySQLバージョンの互換性

MySQLのWITH句は、MySQL 8.0以降でサポートされています。それ以前のバージョンではWITH句を使用できないため、代替手段を考慮する必要があります。

代替手段:

  • サブクエリの利用
    WITH句の代わりにサブクエリを直接使用します。
  SELECT AVG(total_sales)
  FROM (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ) AS sales;
  • ビューの作成
    再利用可能なクエリが必要な場合は、ビューを使用するのも有効です。
  CREATE VIEW sales_view AS
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id;

  SELECT AVG(total_sales) FROM sales_view;

WITH句の適切な使い方

  1. 読みやすさを重視
    WITH句を使う目的はクエリを整理し、可読性を向上させることです。無理に使いすぎると却ってクエリが複雑化するため、必要な場合にのみ使用しましょう。
  2. パフォーマンスの検証
    実行計画（EXPLAINコマンド）を確認し、パフォーマンスを最適化する方法を検討します。
   EXPLAIN
   WITH sales AS (
     SELECT product_id, SUM(amount) AS total_sales
     FROM orders
     GROUP BY product_id
   )
   SELECT * FROM sales WHERE total_sales > 1000;

6. 実務での応用例

売上データの集計

売上データを月ごとに集計し、さらにその結果を利用して、月間平均売上を計算する例です。

例: 月別売上の集計と平均の計算

WITH monthly_sales AS (
  SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY sales_month
)
SELECT 
  sales_month, 
  total_sales,
  AVG(total_sales) OVER () AS average_sales
FROM monthly_sales;

このクエリでは、monthly_salesで月ごとの売上を計算し、それを元に全体の平均売上を計算しています。これにより、データを分かりやすく整理し、分析を簡単に行えます。

特定の条件に基づくデータのフィルタリング

複雑な条件を伴うフィルタリングをWITH句で分けることで、可読性を向上させることができます。

例: 高額購入顧客リストの作成

WITH customer_totals AS (
  SELECT 
    customer_id, 
    SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT 
  customer_id, 
  total_spent
FROM customer_totals
WHERE total_spent > 100000;

このクエリでは、customer_totalsで顧客ごとの購入総額を計算し、その中から条件に合致する高額購入顧客を抽出しています。

階層構造データの分析

組織やカテゴリの階層データを分析する際には、再帰的なWITH句が非常に便利です。

例: 組織内での直属の部下リストの取得

WITH RECURSIVE employee_hierarchy AS (
  SELECT 
    employee_id, 
    name, 
    manager_id, 
    1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT 
    e.employee_id, 
    e.name, 
    e.manager_id, 
    eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT 
  employee_id, 
  name, 
  manager_id, 
  level
FROM employee_hierarchy
ORDER BY level, manager_id;

このクエリでは、employee_hierarchyで階層データを構築し、従業員のレベルごとにリストを取得します。これにより、組織図のような情報を動的に生成できます。

複数の共通テーブル式を活用した分析

複数のWITH句を活用することで、段階的にデータを処理し、複雑な分析をシンプルに実現できます。

例: 商品カテゴリごとの売上上位商品を抽出

WITH category_sales AS (
  SELECT 
    category_id, 
    product_id, 
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY category_id, product_id
),
ranked_sales AS (
  SELECT 
    category_id, 
    product_id, 
    total_sales,
    RANK() OVER (PARTITION BY category_id ORDER BY total_sales DESC) AS rank
  FROM category_sales
)
SELECT 
  category_id, 
  product_id, 
  total_sales
FROM ranked_sales
WHERE rank <= 3;

このクエリでは、カテゴリごとの売上を計算し、その中でトップ3の商品を抽出しています。特定の条件でデータを絞り込む場合に有効です。

実務での応用時のポイント

  1. クエリの分割設計
    WITH句を活用してクエリを分割し、段階的にデータを処理することで、可読性を保ちながら複雑な分析を行えます。
  2. 必要なデータのみを抽出
    WHERE句やLIMIT句を活用して、無駄なデータを処理しないようにすることで、効率的なクエリ設計が可能です。
  3. 業務での柔軟な活用
    売上分析、顧客セグメンテーション、在庫管理など、業務に応じた柔軟な活用が可能です。

7. FAQ（よくある質問）

Q1: WITH句はどのような場合に使用すべきですか？

A1:
WITH句は、以下のようなシナリオで特に有効です：

  • 複雑なサブクエリを簡潔に記述したい場合。
  • 複数のクエリで同じデータセットを繰り返し使用する場合。
  • クエリを論理的に分割して可読性を向上させたい場合。

例えば、同じ集計結果を複数回使用するクエリでは、WITH句を使うことで効率的に処理できます。

Q2: 再帰CTEはどのような場合に役立ちますか？

A2:
再帰CTEは、階層構造や繰り返し計算が必要な場合に役立ちます。具体的には以下のようなシナリオで使用されます：

  • 階層データの処理（例: 組織ツリー、カテゴリ構造の展開）。
  • フォルダやファイルの階層表示。
  • 数値や期間の逐次計算（例: フィボナッチ数列の計算）。

再帰CTEを使えば、自己参照的なデータを簡単に展開し、処理できます。

Q3: WITH句を使用したクエリはビューよりも効率的ですか？

A3:
ケースによります。

  • WITH句は一時的な結果セットを作成し、そのクエリ内でのみ使用されます。頻繁に再利用する必要がないデータに適しています。
  • ビューはデータベース内に永続的に保存され、他のクエリで再利用可能です。繰り返し使用するクエリに適しています。

必要に応じて使い分けることが重要です。

Q4: WITH句の使用でパフォーマンスが低下する原因は何ですか？

A4:
WITH句を使用する際の主なパフォーマンス低下の原因は以下の通りです：

  • CTEの再計算: WITH句の結果が複数回参照されるたびに再計算が行われるため、処理時間が増加します。
  • 大量データの処理: WITH句内で大量のデータを生成すると、メモリ使用量が増え、パフォーマンスが低下します。
  • 適切なインデックスがない: WITH句内のクエリに適切なインデックスがない場合、処理速度が遅くなる可能性があります。

対策:

  • 再利用頻度が高い場合は一時テーブルやビューを検討する。
  • WHERE句やLIMIT句を使用してデータを適切に絞り込む。

Q5: WITH句をサポートしていないMySQLバージョンで代替手段はありますか？

A5:
MySQL 8.0以前のバージョンではWITH句がサポートされていないため、以下の代替手段を使用します：

  • サブクエリの利用
    WITH句の代わりに、サブクエリを直接使用します。
  SELECT AVG(total_sales)
  FROM (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ) AS sales;
  • 一時テーブルの利用
    再利用するデータセットを一時テーブルに保存して対応します。
  CREATE TEMPORARY TABLE temp_sales AS
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id;

  SELECT AVG(total_sales) FROM temp_sales;

Q6: WITH句を使う際のベストプラクティスは何ですか？

A6:
WITH句を使う際のベストプラクティスとして以下の点に留意しましょう：

  • 簡潔さを重視: 複雑なクエリを無理にWITH句に詰め込まず、適切に分割して整理する。
  • パフォーマンスの検証: 実行計画（EXPLAINコマンド）を確認し、必要に応じてクエリを最適化する。
  • 再利用性の検討: 再利用頻度が高い場合はビューや一時テーブルを活用する。

8. まとめ

この記事では、MySQL 8.0で導入されたWITH句（共通テーブル式、CTE）について、基礎から応用まで幅広く解説しました。WITH句は、複雑なクエリを簡潔で読みやすくするための非常に便利な機能です。以下に本記事の重要なポイントをまとめます。

WITH句の主なメリット

  1. クエリの可読性向上
    複雑なサブクエリを簡潔に整理することで、SQLコードの読みやすさと保守性を向上させます。
  2. クエリの再利用性
    同じデータセットを複数回参照する場合に効率的に処理できます。
  3. 再帰的なデータ操作が可能
    再帰CTEを活用することで、階層データや繰り返し計算をシンプルに処理できます。

実務での活用ポイント

  • 売上や顧客データの分析に役立ち、結果を段階的に集計できます。
  • 階層構造のデータ処理（組織図やカテゴリ構造など）では再帰CTEを効果的に使用可能です。
  • 必要に応じてビューや一時テーブルと組み合わせることで、柔軟かつ効率的なデータベース操作が実現します。

使用時の注意点

  • WITH句は便利ですが、適切に使用しなければパフォーマンスが低下する可能性があります。
  • 再利用性やパフォーマンスを考慮して、ケースバイケースでビューや一時テーブルとの使い分けを検討しましょう。
  • 実行計画（EXPLAINコマンド）を確認してクエリの効率を検証することが重要です。

次のステップ

WITH句を使えば、より効率的でメンテナンス性の高いSQLクエリを作成することができます。ぜひ実際のプロジェクトで試してみてください。以下のステップで実践していきましょう：

  1. シンプルなクエリから始めて、WITH句を使った構造化を練習する。
  2. 再帰CTEを使って階層データや複雑なシナリオに挑戦する。
  3. パフォーマンスの最適化を意識して、SQLスキルをさらに向上させる。

以上で、本記事の内容は完了です。ぜひ、MySQL WITH句の知識を日々の業務や学習に活かしてください。