SQLサブクエリの使い方【WHERE・SELECT・FROMの中にSELECTを書く】
「平均より金額が大きい注文だけを取り出したい」「ゴールド会員の注文だけを集計したい」──こうしたときは、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句・サブクエリまでを組み合わせれば、実務で必要な集計のほとんどに対応できます。
NEWSLETTER
Data Schoolの新着記事をメールで受け取る
新しい記事が公開されたら、メールでお知らせします。配信はいつでも停止できます。
