SQL練習室
← SQLガイド

SQLのサブクエリとは？内側のSELECTと実行結果から学ぶ使い方

サブクエリは、SQLの中に書く別のSELECT文です。「平均金額より高い注文」のように、別の検索結果を条件に使えます。まず内側のSELECTだけを実行し、その値を外側でどう使うかを見てみましょう。

例で使う表

customersは顧客の表、ordersは注文の表です。orders.customer_idで注文した顧客が分かります。青木商店には2件、北野物産には1件の注文があり、中央商事には注文がありません。どの例もこの2つの表を使います。結果表の行の並びは一例です。ORDER BYで指定しない限り、表示順は保証されません。

customers：顧客3件の一覧
id（顧客ID）name（顧客名）
1青木商店
2北野物産
3中央商事
orders：注文3件の一覧（金額は円）
id（注文ID）customer_id（顧客ID）amount（金額）
101112000
10218000
103225000

サブクエリの基本構文

sql 
SELECT id, amount
FROM orders
WHERE amount > (
  SELECT AVG(amount)
  FROM orders
);

まず内側のSELECTで平均注文額を求める

注文額の合計は12000 + 8000 + 25000で45000円です。AVG(amount)で3件の平均を求めると15000円になります。この1行1列の結果を、次の節で比較の基準に使います。

sql 
SELECT AVG(amount) AS average_amount
FROM orders;
実行結果
average_amount
15000

求めた値を条件にして注文を探す

平均の15000円より高い注文を、いったん数値を直接書いて探します。12000円と8000円は外れ、25000円の注文103だけが残ります。このSQLでは、平均が変わるたびに15000を書き直す必要があります。

sql 
SELECT id, amount
FROM orders
WHERE amount > 15000;
実行結果
idamount
10325000

数値の代わりにサブクエリを入れる

前の15000を、平均を求めるSELECTで置き換えます。括弧の中がサブクエリです。今の表では平均15000円と比較するので同じ注文103が残りますが、注文が増えても、そのときの平均が比較に使われます。

sql 
SELECT id, amount
FROM orders
WHERE amount > (
  SELECT AVG(amount)
  FROM orders
);
実行結果
idamount
10325000

INで複数行の候補を条件にする

注文した顧客のIDを内側で取り出すと、1、1、2になります。INはその候補にcustomers.idが含まれるかを調べます。青木商店と北野物産が残り、中央商事は外れます。候補に1が2回あっても、青木商店の結果は1行です。

sql 
SELECT id, name
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
);
実行結果
idname
2北野物産
1青木商店

FROMにサブクエリを置いて集計表として使う

内側では注文を顧客IDごとに数えます。顧客1が2件、顧客2が1件の集計表です。この表にtotalsという別名を付け、外側のWHEREで2件以上を選びます。結果は顧客1の1行です。今回はHAVINGでも書けますが、検索結果を表として使う形を確認しましょう。

sql 
SELECT customer_id, order_count
FROM (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
) AS totals
WHERE order_count >= 2;
実行結果
customer_idorder_count
12

EXISTSでその顧客の注文があるかを調べる

内側のWHEREが、外側のcustomers.idを参照しています。顧客1には2行、顧客2には1行の注文が見つかるので、どちらも残ります。EXISTSは1行でも見つかれば成立します。SELECT 1は存在確認用の書き方で、注文数を数えているわけではありません。

sql 
SELECT customers.id, customers.name
FROM customers
WHERE EXISTS (
  SELECT 1
  FROM orders
  WHERE orders.customer_id = customers.id
);
実行結果
idname
2北野物産
1青木商店

NOT EXISTSで注文がない顧客を選ぶ

EXISTSの前にNOTを付けると、内側に1行も見つからない顧客を選べます。顧客3の中央商事だけが残ります。前の例と同じく、orders.customer_id = customers.idが「その顧客の注文」を調べるための条件です。

sql 
SELECT customers.id, customers.name
FROM customers
WHERE NOT EXISTS (
  SELECT 1
  FROM orders
  WHERE orders.customer_id = customers.id
);
実行結果
idname
3中央商事

実際に1問解いてみる

スカラーサブクエリ

全社員の平均給与を超える社員を取得してください。行順は問いません。

テーブル

読み込み中…

実行結果
サブクエリの本格演習へ →

関連構文

SELECTWHEREGROUP BYINNER JOIN

サブクエリについてよくある質問

サブクエリが複数行を返すとエラーになるのはなぜですか？
amount > (SELECT ...)の右側のように1つの値が必要な場所では、1列・最大1行である必要があります。複数の候補と照合したい場合はINを使います。0行の場合は値がNULLになります。
相関サブクエリとは何ですか？
本文のEXISTSの例のように、内側のSQLが外側の行の値を参照するサブクエリです。顧客ごとに注文の有無を考えると意味を追えますが、実際の実行手順はデータベースが決めます。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。