サブクエリを使いこなす

サブクエリとは

サブクエリ? とは、別のSQL文の中に埋め込まれたSELECT文のことです。複雑な条件や集計結果を参照する際に非常に便利です。サブクエリが返す値の種類によって3種類に分類されます。

図1: サブクエリの3分類（スカラー・行・テーブル）

スカラーサブクエリ（1つの値を返す）

スカラーサブクエリは、厳密に1行1列の値を返すサブクエリです。SELECT句、WHERE句、HAVING句など多くの場所で使えます。

-- SELECT句でのスカラーサブクエリ
-- 各商品の価格と全商品の平均価格を並べて表示
SELECT
    name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price,
    price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

-- WHERE句でのスカラーサブクエリ
-- 平均価格より高い商品だけ取得
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

スカラーサブクエリが2行以上を返すとエラーになります。確実に1件しか返らないことがわかっている場合（主キーで絞り込む場合など）に使いましょう。

行サブクエリ（複数列の1行を返す）

行サブクエリは複数列を持つ1行を返します。複数列の比較を一度に行いたいときに便利です。

-- 最新の注文と同じ customer_id と product_id を持つ注文を検索
SELECT *
FROM orders
WHERE (customer_id, product_id) = (
    SELECT customer_id, product_id
    FROM orders
    ORDER BY created_at DESC
    LIMIT 1
);

テーブルサブクエリ（複数行を返す）

テーブルサブクエリは複数行を返し、主に FROM句や IN演算子の中で使われます。

FROM句のサブクエリ（導出テーブル）

FROM句に書いたサブクエリは「導出テーブル」または「インラインビュー」とも呼ばれます。

-- まず部門別平均給与を計算し、それを元にフィルタリング
SELECT dept_name, avg_salary
FROM (
    SELECT
        department_id,
        department_name AS dept_name,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id, department_name
) AS dept_stats
WHERE avg_salary > 500000;

IN演算子とサブクエリ

-- 2025年に注文した顧客の情報を取得
SELECT *
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE YEAR(created_at) = 2025
);

相関サブクエリ（外部クエリを参照する）

図2: 相関サブクエリは外部クエリの各行ごとに内部クエリが実行される

相関サブクエリは外部クエリの列を参照するサブクエリです。外部クエリの行が変わるたびにサブクエリが再評価されるため、「外部クエリの各行に対してサブクエリが1回ずつ実行される」という動作をします。

-- 各部門の平均給与より高い給与の社員を取得
SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id  -- ← 外部クエリの列を参照
);

このクエリは e1 の各行に対してサブクエリを実行し、その行の部門内平均給与を計算します。外部クエリの e1.department_id が変わるたびにサブクエリが再実行されます。

EXISTS / NOT EXISTS

EXISTS は相関サブクエリと組み合わせて「サブクエリが1件以上の結果を返すか」を判定します。サブクエリの実際の値は不要で、存在確認だけが目的です。

-- 1回以上注文したことがある顧客を取得
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1  -- SELECT * でも SELECT 1 でも結果は同じ
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

NOT EXISTS は逆に「1件も存在しない」行を返します。

-- 一度も注文していない顧客を取得
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

サブクエリ vs JOIN のパフォーマンス

「サブクエリとJOINはどちらが速いか」はよく議論になります。現代のデータベースオプティマイザは高度に発達しており、多くの場合は同等のパフォーマンスに最適化されます。しかし一般的な傾向として次のことが言えます。

JOIN が有利なケース

大量データの結合では、オプティマイザがインデックスやHash Joinを効果的に使えるため、JOINの方が高速になることが多いです。

-- サブクエリ版（遅くなる可能性あり）
SELECT *
FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders WHERE amount > 10000
);

-- JOIN版（多くの場合こちらが速い）
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > 10000;

相関サブクエリは要注意

相関サブクエリは外部クエリの行数だけサブクエリが実行されるため、行数が多いと非常に遅くなります。可能であれば JOIN や CTE での書き換えを検討してください。

-- 相関サブクエリ（外部クエリの行数分実行）
SELECT name, (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS cnt
FROM customers c;

-- JOIN版に書き換え（1回の集計で済む）
SELECT c.name, COALESCE(o.cnt, 0) AS cnt
FROM customers c
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS cnt
    FROM orders
    GROUP BY customer_id
) o ON c.id = o.customer_id;

まとめ

スカラーサブクエリ: 1値を返す。SELECT句・WHERE句で使う
行サブクエリ: 複数列の1行を返す。複数列の等値比較に使う
テーブルサブクエリ: 複数行を返す。FROM句やINに使う
相関サブクエリ: 外部クエリの列を参照。行ごとに再実行される
EXISTS/NOT EXISTS: 存在確認に使い、パフォーマンスが良いことが多い
相関サブクエリは行数が多いと遅いため、JOIN への書き換えを検討

次回は CTE（共通テーブル式）? を使ってクエリをより読みやすく整理する方法を解説します。