2025年10月10日金曜日

SQL Server サブクエリとCTEの使い分けと実務活用

SQL Serverで理解すべきサブクエリとCTEの使い分けと実務活用

サブクエリとCTE（共通テーブル式）は、どちらも一時的な結果セットを作成して再利用する方法です。
複雑なSQLを整理し、読みやすく保守しやすくするための重要な構文です。
本記事では、サブクエリとCTEの違い、使いどころ、実務での最適な選び方を具体的な例で解説します。

目次

  1. サブクエリとは
  2. サブクエリの種類と実務例
  3. CTE（共通テーブル式）とは
  4. サブクエリとCTEの違い
  5. 実務での使い分けと最適化指針
  6. 実務課題と回答例
  7. まとめ

サブクエリとは

サブクエリは「SQL文の中でSQLを入れ子にする」構文で、副問い合わせとも呼ばれます。
SELECT・FROM・WHEREなど、あらゆる場所に記述でき、主に一時的な集計・絞り込み・存在確認などに使われます。


-- 基本構文（FROM句の派生テーブル）
SELECT d.CustomerId, d.TotalAmount
FROM (
  SELECT CustomerId, SUM(Amount) AS TotalAmount
  FROM dbo.Orders
  GROUP BY CustomerId
) AS d
INNER JOIN dbo.Customers AS c
  ON d.CustomerId = c.CustomerId;

この例では、注文テーブルを集計して「顧客ごとの合計金額」を求め、その結果を顧客テーブルと結合しています。

サブクエリの種類と実務例

サブクエリにはいくつかの使い方があります。ここでは代表的な4種類を紹介します。

① スカラサブクエリ（単一値を返す）


SELECT c.CustomerId,
       (SELECT MAX(o.OrderDate)
        FROM dbo.Orders AS o
        WHERE o.CustomerId = c.CustomerId) AS LastOrderDate
FROM dbo.Customers AS c;

② INサブクエリ（集合比較）


SELECT CustomerId, FullName
FROM dbo.Customers
WHERE CustomerId IN (
  SELECT CustomerId
  FROM dbo.Orders
  WHERE Amount >= 50000
);

③ EXISTSサブクエリ（存在確認）


SELECT CustomerId, FullName
FROM dbo.Customers AS c
WHERE EXISTS (
  SELECT 1
  FROM dbo.Orders AS o
  WHERE o.CustomerId = c.CustomerId
);

④ FROM句の派生テーブル


SELECT d.CustomerId, d.TotalAmount
FROM (
  SELECT CustomerId, SUM(Amount) AS TotalAmount
  FROM dbo.Orders
  GROUP BY CustomerId
) AS d
INNER JOIN dbo.Customers AS c
  ON d.CustomerId = c.CustomerId;

実務例：顧客ごとの売上ランキングを表示するケースでは、サブクエリで一時集計を行うとシンプルに書けます。


SELECT TOP 10 c.FullName, t.TotalAmount
FROM (
  SELECT CustomerId, SUM(Amount) AS TotalAmount
  FROM dbo.Orders
  GROUP BY CustomerId
) AS t
INNER JOIN dbo.Customers AS c
  ON t.CustomerId = c.CustomerId
ORDER BY t.TotalAmount DESC;

CTE（共通テーブル式）とは

CTE（Common Table Expression）は、WITH句を使って一時的な結果セットを名前付きで定義する構文です。
複雑なクエリを階層的に整理でき、サブクエリよりも可読性・再利用性が高くなります。


-- 基本構文（集計CTE）
WITH OrderSummary AS (
  SELECT CustomerId, SUM(Amount) AS TotalAmount
  FROM dbo.Orders
  GROUP BY CustomerId
)
SELECT c.FullName, s.TotalAmount
FROM dbo.Customers AS c
INNER JOIN OrderSummary AS s
  ON c.CustomerId = s.CustomerId;

🔹 再帰CTE（階層構造の探索）


WITH DepartmentCTE AS (
  SELECT DepartmentId, DepartmentName, ParentDepartmentId, 0 AS Level
  FROM dbo.Departments
  WHERE ParentDepartmentId IS NULL

  UNION ALL

  SELECT d.DepartmentId, d.DepartmentName, d.ParentDepartmentId, c.Level + 1
  FROM dbo.Departments AS d
  INNER JOIN DepartmentCTE AS c
    ON d.ParentDepartmentId = c.DepartmentId
)
SELECT * FROM DepartmentCTE;

CTEは一度定義すれば、その名前を使って複数回参照可能です。
これにより、同じ集計を何度も書く必要がなくなります。

🔹 実務例：日次売上を週単位で集計


WITH DailySales AS (
  SELECT CAST(OrderDate AS date) AS SalesDate, SUM(Amount) AS DailyTotal
  FROM dbo.Orders
  GROUP BY CAST(OrderDate AS date)
)
SELECT DATEPART(week, SalesDate) AS WeekNo, SUM(DailyTotal) AS WeeklyTotal
FROM DailySales
GROUP BY DATEPART(week, SalesDate)
ORDER BY WeekNo;

サブクエリとCTEの違い

サブクエリとCTEは同じように使える場合もありますが、特徴と得意分野が異なります。

比較項目サブクエリCTE
再利用性不可（1回限り）名前で複数回参照可能
可読性ネストが深くなると低下構造的で読みやすい
再帰処理不可再帰CTEで可能
実行順序最適化されるが明示性なし上から順に論理的に処理される
用途小規模・単発の処理複雑・再利用・階層構造

実務での使い分けと最適化指針

  • 単純な集計・絞り込み：サブクエリで十分。
  • 複雑な分析や再利用：CTEで構造化。
  • 階層構造の処理：再帰CTEを使用。
  • パフォーマンス：スカラサブクエリは過剰使用に注意（繰り返し実行される）。
  • CTEの注意点：ネストが多いと最適化されずメモリ消費が増加する場合がある。
  • 共通化と保守性：可読性を重視し、後から見ても理解できる構造を優先。

実務課題と回答例

🔹 課題1：スカラサブクエリで最終注文日を取得


SELECT c.CustomerId, c.FullName,
       (SELECT MAX(OrderDate)
        FROM dbo.Orders AS o
        WHERE o.CustomerId = c.CustomerId) AS LastOrder
FROM dbo.Customers AS c;

🔹 課題2：INサブクエリで高額注文を行った顧客を抽出


SELECT CustomerId, FullName
FROM dbo.Customers
WHERE CustomerId IN (
  SELECT CustomerId
  FROM dbo.Orders
  WHERE Amount >= 100000
);

🔹 課題3：FROM句の派生テーブルで集計


SELECT s.CustomerId, c.FullName, s.TotalAmount
FROM (
  SELECT CustomerId, SUM(Amount) AS TotalAmount
  FROM dbo.Orders
  GROUP BY CustomerId
) AS s
INNER JOIN dbo.Customers AS c
  ON s.CustomerId = c.CustomerId
ORDER BY s.TotalAmount DESC;

🔹 課題4：CTEで顧客ごとの平均購入額を算出


WITH AvgAmount AS (
  SELECT CustomerId, AVG(Amount) AS AvgPurchase
  FROM dbo.Orders
  GROUP BY CustomerId
)
SELECT c.FullName, a.AvgPurchase
FROM dbo.Customers AS c
INNER JOIN AvgAmount AS a
  ON c.CustomerId = a.CustomerId;

🔹 課題5：再帰CTEで部門の階層構造を取得


WITH DepartmentCTE AS (
  SELECT DepartmentId, DepartmentName, ParentDepartmentId, 0 AS Level
  FROM dbo.Departments
  WHERE ParentDepartmentId IS NULL

  UNION ALL

  SELECT d.DepartmentId, d.DepartmentName, d.ParentDepartmentId, c.Level + 1
  FROM dbo.Departments AS d
  INNER JOIN DepartmentCTE AS c
    ON d.ParentDepartmentId = c.DepartmentId
)
SELECT * FROM DepartmentCTE
ORDER BY Level, DepartmentId;

まとめ

サブクエリとCTEはSQL Serverで複雑なデータ処理を整理するための重要構文です。
- サブクエリ：小規模・単発の集計や条件指定に最適
- CTE：複雑な集計や階層処理、再利用を前提とする場合に最適
- 再帰CTEを使えばツリー構造や階層データも簡潔に取得可能
使い分けの基準は「可読性・再利用性・処理コスト」の3点で判断しましょう。

SQL Server学習シリーズ

参考リンク

SQL Server 解説用イメージ

補足：サブクエリとCTEはどちらも便利な構文ですが、最適化の挙動が異なります。
複雑なクエリでは実行計画を確認し、適切な方法を選択することでパフォーマンスと保守性を両立できます。

※2025/10/31　更新
on
Labels: ,

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)