SQL

SQLサブクエリの使い方【WHERE・SELECT・FROMの中にSELECTを書く】

酒井 裕麻株式会社Bistro 代表取締役社長

「平均より金額が大きい注文だけを取り出したい」「ゴールド会員の注文だけを集計したい」──こうしたときは、SQLの中に、もう1つSQLを書くことで解決できます。この、SQLの中に書くSQLを サブクエリ(副問い合わせ) と呼びます。

この記事では、サブクエリを書く場所ごとに、使い方と実行結果を解説します。最後に、WITH句・JOIN・ウィンドウ関数との使い分けも整理します。

参考記事

SQL EXISTS句の使い方【NOT EXISTS・INとの違い・実行例付き】
SQL WITH句(CTE)の使い方【長いクエリを読みやすく組み立てる】
SQLウィンドウ関数の使い方【ROW_NUMBER・RANK・LAG・累計・移動平均】

この記事で使うデータ

JOINの記事と同じ、架空のお店の顧客テーブル(shop.customers)と注文テーブル(shop.orders、全12件)を使います。BigQueryで同じデータを作る場合は、サンプルデータのSQL(shop_sample.sql)を実行してください。

サブクエリを書ける場所

書く場所 サブクエリが返すもの 例
WHEREの中 1つの値、または値の一覧 平均より大きい注文、ゴールド会員の注文
SELECTの中 1つの値 各行に全体の平均を並べる
FROMの中 表 顧客ごとに集計してから、さらにランク別に集計する

サブクエリは、必ず ( )(カッコ)で囲んで書きます。

WHEREの中:平均より大きい注文を取り出す

まず、注文金額の平均を確認します。

SELECT
  AVG(amount) AS avg_amount
FROM
  shop.orders
avg_amount
2891.6666666666665

この平均値を、WHEREの中のサブクエリとして使います。

SELECT
  order_id
  , customer_id
  , amount
FROM
  shop.orders
WHERE
  amount > (SELECT AVG(amount) FROM shop.orders)
ORDER BY
  amount DESC

実行結果

order_id customer_id amount
1009 C05 6800
1005 C05 5400
1001 C01 4400
1004 C03 3600
1011 C07 3000

カッコの中の SELECT AVG(amount) FROM shop.orders が先に計算され、約2,892円という1つの値になってから、外側のWHEREで比べられます。平均の値をあらかじめ調べて WHERE amount > 2892 と書くのと違い、データが増えても自動で最新の平均と比べてくれるのがメリットです。

このように1つの値だけを返すサブクエリを、スカラーサブクエリと呼びます。

WHEREの中:INで、別のテーブルの条件で絞り込む

ゴールド会員の注文だけを取り出します。会員ランクは顧客テーブルにあるので、サブクエリでゴールド会員の顧客IDの一覧を作り、IN で絞り込みます。

SELECT
  order_id
  , customer_id
  , amount
FROM
  shop.orders
WHERE
  customer_id IN (
    SELECT customer_id
    FROM shop.customers
    WHERE rank = 'ゴールド'
  )
ORDER BY
  order_id

実行結果

order_id customer_id amount
1001 C01 4400
1003 C01 2500
1005 C05 5400
1009 C05 6800
1012 C01 1200

サブクエリが「C01, C05」という値の一覧を返し、外側のSQLで、その一覧に含まれる注文だけが残ります。同じことは EXISTS やJOINでも書けます。「含まれない」ものを探すときは、NOT IN よりも NOT EXISTS を使う方が安全です(EXISTS句の記事で解説しています)。

SELECTの中:各行に全体の値を並べる

SELECTの中にスカラーサブクエリを書くと、すべての行に同じ値(ここでは全体の平均)を並べられます。

SELECT
  order_id
  , amount
  , ROUND((SELECT AVG(amount) FROM shop.orders)) AS avg_amount
  , ROUND(amount - (SELECT AVG(amount) FROM shop.orders)) AS diff
FROM
  shop.orders
WHERE
  order_id <= 1004
ORDER BY
  order_id

実行結果

order_id amount avg_amount diff
1001 4400 2892 1508
1002 1200 2892 -1692
1003 2500 2892 -392
1004 3600 2892 708

ここで注目したいのは、外側のWHEREで order_id <= 1004 に絞っていても、サブクエリの平均は12件全体で計算されていることです。サブクエリは外側の条件とは関係なく、カッコの中に書かれた条件だけで計算されます。

FROMの中:集計してから、さらに集計する

FROMの中にサブクエリを書くと、サブクエリの結果を1つの表として扱えます。「顧客ごとの売上を出してから、会員ランク別に平均する」のような、2段階の集計に使います。

SELECT
  rank_name
  , COUNT(*) AS customers
  , ROUND(AVG(sales)) AS avg_sales
FROM (
  SELECT
    c.customer_id
    , c.rank AS rank_name
    , SUM(o.amount) AS sales
  FROM
    shop.customers AS c
    INNER JOIN shop.orders AS o
      ON c.customer_id = o.customer_id
  GROUP BY
    c.customer_id
    , c.rank
)
GROUP BY
  rank_name
ORDER BY
  avg_sales DESC

実行結果

rank_name customers avg_sales
ゴールド 2 10150
シルバー 2 3700
一般 2 3200

購入のあったゴールド会員の1人あたりの売上は10,150円で、ほかのランクの約3倍です。

いきなりランク別に AVG(o.amount) を計算すると、「注文1件あたりの平均」になってしまいます。「顧客1人あたりの平均」を出したいときは、このように一度顧客ごとに集計してから平均するのがポイントです。

相関サブクエリ:外側の行ごとに計算する

サブクエリの中で外側のテーブルの列を使うと、外側の1行ごとにサブクエリが計算されます。これを相関サブクエリと呼びます。

顧客ごとの注文数と最終注文日

SELECT
  c.customer_id
  , c.name
  , (SELECT COUNT(*) FROM shop.orders AS o WHERE o.customer_id = c.customer_id) AS orders
  , (SELECT MAX(order_date) FROM shop.orders AS o WHERE o.customer_id = c.customer_id) AS last_order
FROM
  shop.customers AS c
ORDER BY
  c.customer_id

実行結果

customer_id name orders last_order
C01 佐藤 3 2026-03-29
C02 鈴木 2 2026-03-14
C03 高橋 2 2026-03-22
C04 田中 0 NULL
C05 伊藤 2 2026-03-21
C06 渡辺 1 2026-03-15
C07 山本 1 2026-03-28
C08 中村 0 NULL

WHERE o.customer_id = c.customer_id の部分で、外側の顧客(c)とつないでいます。注文のない顧客は、件数が0、最終注文日がNULLになります。

店舗ごとの平均より大きい注文

相関サブクエリはWHEREの中でも使えます。その注文の店舗の平均より金額が大きい注文を取り出してみます。

SELECT
  o.order_id
  , o.store
  , o.amount
FROM
  shop.orders AS o
WHERE
  o.amount > (
    SELECT AVG(o2.amount)
    FROM shop.orders AS o2
    WHERE o2.store = o.store
  )
ORDER BY
  o.store
  , o.order_id

実行結果

order_id store amount
1005 オンライン 5400
1009 オンライン 6800
1006 横浜店 2800
1011 横浜店 3000
1001 渋谷店 4400
1004 渋谷店 3600

店舗ごとの平均は、オンラインが3,825円、横浜店が約2,333円、渋谷店が2,480円です。全体の平均(約2,892円)と比べた場合と違い、横浜店の2,800円の注文も「店舗の平均より大きい」として残っています。同じテーブルを内側と外側で使うので、o と o2 のように別名を分けるのがポイントです。

サブクエリ・WITH句・JOIN・ウィンドウ関数の使い分け

同じ集計でも、書き方は1つではありません。

やりたいこと おすすめの書き方
全体の平均などの1つの値と比べる スカラーサブクエリ
別のテーブルにあるかどうかで絞り込む IN・EXISTS(含まないものは NOT EXISTS)
集計してからさらに集計する(2段階まで) FROMの中のサブクエリ
3段階以上の処理、同じ集計を何度も使う WITH句
別のテーブルの列も一緒に表示したい JOIN
グループごとの平均・順位・前の行と比べる ウィンドウ関数(AVG(…) OVER (PARTITION BY …) など)

たとえば最後の「店舗ごとの平均より大きい注文」は、ウィンドウ関数を使って次のようにも書けます。

SELECT
  order_id
  , store
  , amount
FROM
  shop.orders
QUALIFY
  amount > AVG(amount) OVER (PARTITION BY store)

データ量が多い場合は、相関サブクエリよりも、JOINやウィンドウ関数で書いた方が速く動くことが多いです。サブクエリが2重・3重の入れ子になってきたら、WITH句に書き直すのを目安にすると、読みやすいSQLを保てます。

まとめ

  • サブクエリは、SQLの中に ( ) で囲んで書く、もう1つのSQL
  • WHEREの中では、1つの値(スカラーサブクエリ)や値の一覧(IN)で絞り込める
  • FROMの中では、サブクエリの結果を表として扱い、2段階の集計ができる
  • 外側の列を使う相関サブクエリで、行ごとの計算ができる
  • 入れ子が深くなったら、WITH句やウィンドウ関数への書き換えを考える

これで、SQLの記事シリーズでよく使う構文はひととおりそろいました。基本のSELECTから、JOIN・CASE・ウィンドウ関数・WITH句・サブクエリまでを組み合わせれば、実務で必要な集計のほとんどに対応できます。

CONTACT

データ活用のお悩み、
直接相談しませんか。

「何から始めればいいかわからない」段階でも大丈夫です。課題の整理からお手伝いします。