SQLite相関サブクエリ：分かりやすく解説！サンプルコード付き

相関サブクエリは、複雑なデータ操作をシンプルに記述できるため、パワフルなツールとなりえます。しかし、理解と使い方が難しいのも事実です。

そこで、このガイドでは、SQLiteにおける相関サブクエリを以下の通り、分かりやすく詳細に解説します。

相関サブクエリの基本構文

相関サブクエリは、以下の基本構文で記述されます。

SELECT
  列名1,
  列名2,
  ...
FROM
  メインテーブル
WHERE
  条件式 (サブクエリ);

例： 特定の部署に属する社員の中で、給与が最も高い社員の氏名と給与を取得する

SELECT
  社員名,
  給与
FROM
  社員
WHERE
  給与 IN (
    SELECT MAX(給与)
    FROM
    社員
    WHERE
    部署ID = 外側のクエリで参照される部署ID
  );

相関サブクエリの実行順序

相関サブクエリは、外側のクエリに対して行ごとに実行されます。つまり、外側のクエリの各行に対して、サブクエリが1回ずつ実行され、その結果が外側のクエリで使用されます。

相関サブクエリの注意点

相関サブクエリを使用する際は、以下の点に注意する必要があります。

• 代替手段
  場合によっては、結合やウィンドウ関数を使用して、相関サブクエリよりも効率的かつ読みやすいコードを書くことができます。
• 読みやすさ
  複雑な相関サブクエリは、コードが読みづらくなります。
• パフォーマンス
  相関サブクエリは、外側のクエリに対して行ごとに実行されるため、処理速度が遅くなる可能性があります。

相関サブクエリの実用例

相関サブクエリは、様々なデータ操作に使用できます。以下に、いくつかの例を紹介します。

• 従業員ごとに、直属の部下の数を取得する
• 顧客ごとに、注文された商品の合計金額を取得する
• 特定のカテゴリに属する製品の中で、販売個数が最も多い製品のIDと販売個数を取得する

---

例1：特定の部署に属する社員の中で、給与が最も高い社員の氏名と給与を取得する

SELECT
  社員名,
  給与
FROM
  社員
WHERE
  給与 IN (
    SELECT MAX(給与)
    FROM
    社員
    WHERE
    部署ID = 外側のクエリで参照される部署ID
  );

例2：顧客ごとに、注文された商品の合計金額を取得する

SELECT
  顧客ID,
  SUM(商品価格 * 注文個数) AS 合計金額
FROM
  注文
  JOIN
  商品
  ON
  注文.商品ID = 商品.商品ID
GROUP BY
  顧客ID;

例3：従業員ごとに、直属の部下の数を取得する

SELECT
  上司ID,
  COUNT(*) AS 部下数
FROM
  社員
WHERE
  上司ID = 外側のクエリで参照される社員ID
GROUP BY
  上司ID;

例4：在庫数に基づいて、再注文が必要な商品をリストアップする

SELECT
  商品ID,
  商品名,
  在庫数
FROM
  商品
WHERE
  在庫数 < (
    SELECT
      MIN(発注点)
    FROM
      発注
  );

例5：過去1年間で注文された商品をリストアップする

SELECT
  商品ID,
  商品名,
  MIN(注文日) AS 最初の発注日
FROM
  注文
WHERE
  注文日 >= (
    SELECT
      CURRENT_DATE - INTERVAL '1 YEAR'
  );

これらの例はほんの一例であり、相関サブクエリを使用して実現できることは他にもたくさんあります。

• 複雑なクエリの場合は、WITH句を使用して中間結果を一時的な表として保存し、可読性とパフォーマンスを向上させることができます。
• 上記の例では、テーブル名やカラム名は仮称です。実際のコードで使用する場合には、適切な名前に置き換えてください。

---

そこで、ここでは、SQLiteにおける相関サブクエリの代替方法についていくつか紹介します。

JOIN

多くの場合、JOINを使用して、相関サブクエリよりも効率的で読みやすいコードを書くことができます。

例： 特定の部署に属する社員の中で、給与が最も高い社員の氏名と給与を取得する

相関サブクエリ

SELECT
  s.社員名,
  s.給与
FROM
  社員 s
WHERE
  s.給与 IN (
    SELECT MAX(給与)
    FROM
    社員
    WHERE
    部署ID = s.部署ID
  );

JOIN

SELECT
  s.社員名,
  s.給与
FROM
  社員 s
JOIN (
  SELECT
    部署ID,
    MAX(給与) AS 最高給与
  FROM
  社員
  GROUP BY
  部署ID
) AS 部署最高給与
ON
  s.部署ID = 部署最高給与.部署ID
WHERE
  s.給与 = 部署最高給与.最高給与;

ウィンドウ関数

ウィンドウ関数は、特定の行範囲にわたって集計や計算を行う関数です。相関サブクエリを置き換えるために使用できる便利なツールです。

例： 特定のカテゴリに属する製品の中で、販売個数が最も多い製品のIDと販売個数を取得する

相関サブクエリ

SELECT
  p.商品ID,
  p.販売個数
FROM
  商品 p
WHERE
  p.販売個数 IN (
    SELECT MAX(販売個数)
    FROM
    商品
    WHERE
    カテゴリID = p.カテゴリID
  );

ウィンドウ関数

SELECT
  商品ID,
  販売個数,
  ROW_NUMBER() OVER (PARTITION BY カテゴリID ORDER BY 販売個数 DESC) AS 順位
FROM
  商品
ORDER BY
  カテゴリID,
  販売個数 DESC;

CTE (Common Table Expressions)

CTEは、一時的な結果セットを定義するために使用できるサブクエリです。複雑な相関サブクエリをより小さな、理解しやすい CTE に分割することで、可読性を向上させることができます。

例： 顧客ごとに、注文された商品の合計金額を取得する

相関サブクエリ

SELECT
  顧客ID,
  SUM(商品価格 * 注文個数) AS 合計金額
FROM
  注文
  JOIN
  商品
  ON
  注文.商品ID = 商品.商品ID
GROUP BY
  顧客ID;

CTE

WITH 注文明細 AS (
  SELECT
    注文ID,
    顧客ID,
    商品ID,
    商品価格,
    注文個数
  FROM
    注文
  JOIN
    商品
  ON
    注文.商品ID = 商品.商品ID
)
SELECT
  顧客ID,
  SUM(商品価格 * 注文個数) AS 合計金額
FROM
  注文明細
GROUP BY
  顧客ID;

サブクエリを結合する

場合によっては、複数のサブクエリを結合することで、相関サブクエリを回避することができます。

例： 従業員ごとに、直属の部下の数を取得する

相関サブクエリ

SELECT
  上司ID,
  COUNT(*) AS 部下数
FROM
  社員
WHERE
  上司ID = 外側のクエリで参照される社員ID
GROUP BY
  上司ID;

結合サブクエリ

SELECT
  s.社員ID AS 上司ID,
  COUNT(*) AS 部下数
FROM
  社員 s
LEFT JOIN
  社員 b
  ON
  s.社員ID = b.上司ID
GROUP BY
  s.社員ID;