SQL Quiz Book

SQL サブクエリ — スカラー・IN/NOT IN・EXISTSの基礎

基礎サブクエリスカラーサブクエリIN / NOT IN / EXISTSFROM句 / 相関CTE比較PostgreSQL対応全5問
1 / 5 · 学習中 0 / 5 問完了
QUESTION 1

スカラーサブクエリ — SELECT句に全体平均を付与して各行と比較する

スカラーSQSELECT句AVG集計KPI分析
前提知識 ▼

サブクエリとは、SQL文の中に書かれた別のSELECT文のことです。スカラーサブクエリは「必ず1行1列（単一値）を返す」サブクエリで、SELECT句・WHERE句など値が使える場所に自由に埋め込めます。

SELECT
  col,
  (SELECT AVG(col) FROM tbl) AS avg_val  -- 全行に同じ集計値を付与
FROM tbl;
スカラーサブクエリのポイント：外側のクエリが動く前に、内側のサブクエリが先に評価されて単一値を返します。その値が全行に展開されるため、全件平均と各行を比較する処理が1クエリで実現できます。
問題 ▼

ordersテーブルから、status が 'completed' の注文について order_id, user_id, amount を取得し、さらに全completed注文の平均金額と平均との差分を列として付与してください。amount の高い順に並べてください。

使用テーブル
▸ orders
order_iduser_idamountstatus
10118000completed
102112000completed
10323500pending
10439500completed
10536000completed
10642000cancelled
期待出力 ▼
order_iduser_idamountavg_amountdiff_from_avg
10211200088753125
104395008875625
101180008875-875
105360008875-2875
模範解答コード ▼ 
SELECT
  order_id,
  user_id,
  amount,
  (SELECT ROUND(AVG(amount))    -- スカラーSQ: 必ず単一値を返す
   FROM   orders
   WHERE  status = 'completed'
  ) AS avg_amount,
  amount -
  (SELECT ROUND(AVG(amount))    -- 算術式の中でも使用可能
   FROM   orders
   WHERE  status = 'completed'
  ) AS diff_from_avg
FROM   orders
WHERE  status = 'completed'     -- 外側クエリで絞り込む
ORDER BY amount DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. スカラーSQ先行評価
       → SELECT ROUND(AVG(amount)) FROM orders WHERE status='completed'
       → 結果: 8875
  2. FROM orders              → 全行読み込み
  3. WHERE status='completed' → 4行に絞り込む
  4. SELECT                   → 各行に avg_amount=8875, diff_from_avg を付与
  5. ORDER BY amount DESC     → 金額降順に並び替え
*/
解説（テーブル変化・ポイント） ▼
SELECT order_id, user_id, amount, ( SELECT ROUND(AVG(amount)) FROM orders WHERE status = 'completed' ) AS avg_amount, amount - ( SELECT ROUND(AVG(amount)) FROM orders WHERE status = 'completed' ) AS diff_from_avg FROM orders WHERE status = 'completed' ORDER BY amount DESC;
LEGEND ▼
評価対象の列・キー
除外・非表示データ
✓ 通過
✗ 除外
① スカラーSQ実行 ▼
SELECT ROUND(AVG(amount)) FROM orders WHERE status='completed'サブクエリが最初に評価され、status='completed' の4件から平均 (8000+12000+9500+6000)÷4 = 8875 を計算し、単一値として返します。この値が外側クエリの全行に付与されます。
1 / 3
order_idamountstatusSQ対象?
1018000completed✓ AVG対象
10212000completed✓ AVG対象
1033500pending✗ 除外
1049500completed✓ AVG対象
1056000completed✓ AVG対象
1062000cancelled✗ 除外
→ ROUND(AVG) = 8875 を返す
学習ポイント
スカラーSQの本質：「1行1列の値を返す SELECT 文」です。数値リテラルや関数の戻り値と同じ位置に書けます。AVG などの集計値はその行に依存しない「定数」として全行に展開されます。
実務パターン – KPI比較クエリ：「各注文額 vs 全体平均」「今月売上 vs 先月平均」など、個別行データ vs 集計値の比較は API ダッシュボード用クエリで最頻出。スカラーSQ を使えば JOIN なしで1クエリに収まります。
実行タイミング：スカラーSQ は外側クエリより先に評価され、結果がキャッシュされて全行に適用されます。つまり (SELECT AVG(amount)...) はテーブルスキャンを1回だけ行い、全行に同じ値 8875 を渡します。
CTE でリファクタリング可能：同一サブクエリを複数箇所に書くと DB によっては2回評価されます。WITH avg_val AS (...) SELECT ..., avg_val.v FROM orders, avg_val とすれば評価1回で済みます（Q9参照）。
アンチパターン
複数行を返すスカラーSQ：(SELECT user_id FROM users WHERE plan='premium') は複数行を返すためスカラーSQ には使えず実行時エラーになります。スカラーSQ は必ず集計関数や LIMIT 1 で単一値に絞ること。
SELECT句での繰り返し記述：同一スカラーSQ を複数箇所に書くと DB によっては2回評価されます。パフォーマンスが気になる場合は CTE（WITH句）でまとめて1回だけ評価させましょう。
実務コラム：スカラーSQ と窓関数の使い分け
スカラーSQ で「全体平均との差」を計算するのと AVG(amount) OVER () というウィンドウ関数は実質等価です。ただしウィンドウ関数が使えない古い DB 環境（MySQL 5.x 等）ではスカラーSQ が唯一の手段です。PostgreSQL・MySQL 8.0+ などではウィンドウ関数が推奨されますが、スカラーSQ はどの RDBMS でも動くポータブルな書き方です。
1 / 5
問題を選択
QUESTION 2

WHERE IN サブクエリ — サブクエリで動的リストを作り注文を絞り込む

WHERE INサブクエリ絞り込みプラン別分析
前提知識 ▼

WHERE col IN (サブクエリ) は、サブクエリが返す値のリストを使って行を絞り込みます。固定値 IN (1, 3) の代わりに、テーブルの状態に応じて動的に変わるリストを生成できるのがポイントです。

SELECT * FROM orders
WHERE  user_id IN (
  SELECT user_id FROM users
  WHERE  plan = 'premium'   -- 内側が動的リストを生成
);
IN の内側は「単一列」を返す必要があります。外側の user_id と照合するため、内側は必ず1列だけ SELECT します。
問題 ▼

orders テーブルから、plan が 'premium' のユーザーの注文を order_id, user_id, amount, status, ordered_at で取得してください。ordered_at 降順で並べてください。

使用テーブル
▸ users
user_idnameplan
1田中 太郎premium
2佐藤 花子free
3鈴木 一郎premium
4山田 次郎standard
5伊藤 三郎free
▸ orders
order_iduser_idamountstatusordered_at
10118000completed2024-05-01
102112000completed2024-05-20
10323500pending2024-05-10
10439500completed2024-05-15
10536000completed2024-05-25
10642000cancelled2024-05-08
期待出力 ▼
order_iduser_idamountstatusordered_at
10536000completed2024-05-25
102112000completed2024-05-20
10439500completed2024-05-15
10118000completed2024-05-01
模範解答コード ▼ 
SELECT
  order_id, user_id, amount, status, ordered_at
FROM   orders
WHERE  user_id IN (        -- IN: サブクエリ結果リストに一致する行を残す
  SELECT user_id            -- 必ず単一列だけを返す
  FROM   users
  WHERE  plan = 'premium'  -- premium のuser_idリストを動的生成
)
ORDER BY ordered_at DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. サブクエリ評価
       → SELECT user_id FROM users WHERE plan='premium'
       → 結果リスト: [1, 3]
  2. FROM orders              → 全行読み込み
  3. WHERE user_id IN (1, 3)  → 4行に絞り込む
  4. SELECT ...               → 必要列を選択
  5. ORDER BY ordered_at DESC → 日付降順に並び替え
*/
解説（テーブル変化・ポイント） ▼
SELECT order_id, user_id, amount, status, ordered_at FROM orders WHERE user_id IN ( SELECT user_id FROM users WHERE plan = 'premium' ) ORDER BY ordered_at DESC;
LEGEND ▼
評価対象の列・キー
除外・非表示データ
✓ 通過
✗ 除外
① サブクエリ実行 ▼
SELECT user_id FROM users WHERE plan = 'premium'サブクエリが先に実行されます。usersからplan='premium'のuser_idを取り出し、値のリスト [1, 3] を生成します。
1 / 3
user_idnameplanリストに含む?
1田中 太郎premium✓ [1,3]に追加
2佐藤 花子free✗ 除外
3鈴木 一郎premium✓ [1,3]に追加
4山田 次郎standard✗ 除外
5伊藤 三郎free✗ 除外
→ IN リスト: [1, 3] 生成
学習ポイント
IN サブクエリの仕組み：IN の内側で返される「値のリスト」は外側クエリより先に確定します。固定値 IN (1, 3) と論理的に等価ですが、テーブルの状態に応じて動的に変わる点が重要です。
JOIN との等価変換：WHERE user_id IN (SELECT user_id FROM users WHERE plan='premium') は INNER JOIN users ON orders.user_id = users.user_id WHERE users.plan='premium' と同じ結果を返します。結合先の列を SELECT に出す必要がなければ IN サブクエリの方が意図が明確です。
NULL 混入に注意：IN リストに NULL が含まれると一致しない行が FALSE でなく UNKNOWN になります。NULL が混入する可能性がある列には内側に WHERE col IS NOT NULL を追加しましょう。
アンチパターン
IN の内側で複数列を返す：SELECT user_id, name FROM users のように複数列を返すと構文エラーになります。IN の比較対象と一致する単一列だけを SELECT しましょう。
大量リストの IN は遅い場合あり：IN のリストが数万件になると評価コストが上がります。大量データ時は EXISTS や INNER JOIN を検討しましょう。
実務コラム：IN / JOIN / EXISTS の使い分け基準
3つはいずれも「別テーブルの条件で絞り込む」ために使います。IN サブクエリは内側リストが小〜中規模で意図が明確なとき。JOINは結合先の列も SELECT に出したいとき。EXISTSは「存在するかどうか」だけ確認し重複を気にしないとき。これが実務の目安です。
2 / 5
問題を選択
QUESTION 3

WHERE NOT IN サブクエリ — 一度も注文していないユーザーを検出する

NOT INサブクエリ休眠検出NULL注意
前提知識 ▼

WHERE col NOT IN (サブクエリ) は、サブクエリの結果に含まれない行を返します。「注文したことがないユーザー」「未配信のキャンペーン対象者」など、別テーブルに存在しない行を見つけるパターンです。

SELECT * FROM users
WHERE user_id NOT IN (
  SELECT DISTINCT user_id FROM orders
  WHERE user_id IS NOT NULL   -- NULL混入防止（重要）
);
NULL の罠（重要）：NOT IN の内側リストに NULL が1つでも含まれると、外側の全行が除外されて0件になります。内側には必ず WHERE col IS NOT NULL を付けるか、NOT EXISTS を使うことを推奨します。
問題 ▼

users テーブルから、一度も注文がないユーザーを取得してください。user_id, name, plan を user_id 昇順で返してください。

使用テーブル
▸ users
user_idnameplan
1田中 太郎premium
2佐藤 花子free
3鈴木 一郎premium
4山田 次郎standard
5伊藤 三郎free
▸ orders（user_id 一覧）
order_iduser_id
1011
1021
1032
1043
1053
1064
期待出力 ▼
user_idnameplan
5伊藤 三郎free
模範解答コード ▼ 
SELECT
  user_id, name, plan
FROM   users
WHERE  user_id NOT IN (   -- リストに含まれない行だけ残す
  SELECT DISTINCT user_id  -- DISTINCT で重複除去（効率化）
  FROM   orders
  WHERE  user_id IS NOT NULL -- NULL混入防止（NOT INの罠を回避）
)
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. サブクエリ評価
       → SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL
       → 結果リスト: [1, 2, 3, 4]
  2. FROM users              → 全行読み込み
  3. WHERE user_id NOT IN (1,2,3,4)
                             → user_id=5 の行だけ残る
  4. SELECT user_id, name, plan  → 必要列を選択
  5. ORDER BY user_id        → user_id 昇順に並び替え
*/
解説（テーブル変化・ポイント） ▼
SELECT user_id, name, plan FROM users WHERE user_id NOT IN ( SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL ) ORDER BY user_id;
LEGEND ▼
評価対象の列・キー
除外・非表示データ
✓ 通過
① サブクエリ実行 ▼
SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULLordersから注文が存在するuser_idを重複なく取得。結果リスト [1,2,3,4] が生成されます。user_id=5 は orders に行が存在しません。
1 / 3
order_iduser_id（DISTINCT後）リスト登録
1011✓ リストに追加
1021（重複 → 無視）
1032✓ リストに追加
1043✓ リストに追加
1053（重複 → 無視）
1064✓ リストに追加
→ NOT IN リスト: [1, 2, 3, 4]
学習ポイント
NOT IN = 差集合：「テーブルAにあってテーブルBにない行を取る」差集合の処理です。「未購入ユーザーへのメール配信対象抽出」などに直結します。
DISTINCT で重複除去：1人のユーザーが複数注文していると user_id が重複しますが、IN 評価では重複は問題になりません。ただし DISTINCT を付けることでリストを小さく保ち、評価コストを下げられます。
NOT EXISTS が安全な代替：NULL の罠を気にしたくない場合は WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.user_id) を使います。NOT EXISTS は NULL があっても期待通り動作します（Q6で詳解）。
アンチパターン
NULL が混入すると全行が除外される：仮に orders.user_id に NULL 行が1件でもあった場合、NOT IN リストは [1, NULL, 2, 3, 4] になります。SQL の三値論理では 5 NOT IN (1, NULL, 2, 3, 4) が UNKNOWN と評価されるため、user_id=5 も除外されて結果が0件になります。
NOT IN vs NOT EXISTS 使い分け：NULL が混入するリスクがある本番データでは、NOT EXISTS を第一選択にする習慣が安全です。
実務コラム：休眠ユーザー・未実施アクションの検出
「一度も注文していないユーザー」「アンケートに未回答の社員」「今月ログインのないユーザー」といった「〜していない」を検出するクエリは運用バッチで頻出です。NOT IN / NOT EXISTS / LEFT JOIN + IS NULL の3パターンを押さえておきましょう。NULL が混入するリスクがある本番データでは NOT EXISTS を第一選択にする習慣が重要です。
3 / 5
問題を選択
QUESTION 4

FROM句サブクエリ（派生テーブル）— 集計結果をさらにWHEREで絞り込む

FROM句SQ派生テーブルGROUP BY売上集計
前提知識 ▼

GROUP BY で集計した結果に対して WHERE で条件を付けたい場合、FROM句にサブクエリを書く「派生テーブル（インラインビュー）」を使います。通常の WHERE 句は GROUP BY より先に評価されるため、集計後の列を直接 WHERE で使えません。派生テーブルがこの制約を解消します。

SELECT *
FROM (
  SELECT col, SUM(amount) AS total  -- 内側で集計
  FROM   table
  GROUP BY col
) AS sub                             -- 必ず AS でエイリアスを付ける
WHERE  total >= 10000;              -- 集計後の値で絞り込める
HAVING との違い：HAVING は GROUP BY と同じクエリ内でしか使えません。派生テーブルは集計結果を「仮想テーブル」として扱うため、さらに他テーブルと JOIN 等の柔軟な処理が可能です。
問題 ▼

orders テーブルから、status が 'completed' の注文のみを対象にユーザーごとの合計金額を集計し、合計金額が 15,000 以上のユーザーのみを合計金額降順で返してください。取得列は user_id, total_amount とします。

使用テーブル
▸ orders
order_iduser_idamountstatus
10118000completed
102112000completed
10323500pending
10439500completed
10536000completed
10642000cancelled
期待出力 ▼
user_idtotal_amount
120000
315500
模範解答コード ▼ 
SELECT
  user_id,
  total_amount
FROM (                           -- FROM句にサブクエリ（派生テーブル）
  SELECT
    user_id,
    SUM(amount) AS total_amount  -- 集計列に AS で名前を付ける（外側から参照）
  FROM   orders
  WHERE  status = 'completed'    -- 内側クエリで絞ってから集計
  GROUP BY user_id
) AS user_totals                 -- 派生テーブルには必ず AS エイリアスを付ける
WHERE  total_amount >= 15000     -- 集計結果の列名で絞り込める（通常WHEREでは不可）
ORDER BY total_amount DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. 内側クエリ（派生テーブル）を評価              → completed を user_id で集計
  2. FROM ... AS user_totals       → 集計結果を参照
  3. WHERE total_amount のしきい値      → 条件で絞る
  4. SELECT user_id, total_amount  → 列を選択
  5. ORDER BY total_amount DESC    → 合計額降順
  */
解説（テーブル変化・ポイント） ▼
SELECT user_id, total_amount FROM ( SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE status = 'completed' GROUP BY user_id ) AS user_totals WHERE total_amount >= 15000 ORDER BY total_amount DESC;
LEGEND ▼
グループ化キー・集計対象
グループ分類
除外・非表示データ
① 内側クエリ（GROUP BY） ▼
SELECT user_id, SUM(amount) FROM orders WHERE status='completed' GROUP BY user_id内側のサブクエリが先に実行。completedの4件をuser_idでグループ化し合計を計算。user_id=2(pending)・4(cancelled)は内側WHEREで除外されます。
1 / 4
order_iduser_idamountstatusグループ
10118000completeduser1グループ
102112000completeduser1グループ
10323500pending除外
10439500completeduser3グループ
10536000completeduser3グループ
10642000cancelled除外
→ 派生テーブル: 2行生成
学習ポイント
派生テーブルとは：FROM句に書いたサブクエリのことを「派生テーブル」または「インラインビュー」と呼びます。外側からは普通のテーブルと同様に扱えます。必ず AS エイリアス名 を付けることが必須です（省略するとエラー）。
「WHEREでは集計列を参照できない」問題を解決：通常の WHERE 句は GROUP BY より先に評価されるため、WHERE SUM(amount) >= 15000 は書けません（エラー）。派生テーブルにして外側 WHERE で絞ることで同じことができ、さらに別テーブルとのJOINなど柔軟な処理に対応できます。
CTE に書き換えると可読性が向上：WITH user_totals AS (SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE status='completed' GROUP BY user_id) SELECT * FROM user_totals WHERE total_amount >= 15000 とするとネストが浅くなります（Q9参照）。
アンチパターン
AS エイリアスを忘れる：FROM句のサブクエリに AS user_totals を付けないと多くのDBでエラーになります（MySQL・PostgreSQL とも必須）。派生テーブルには必ずエイリアスを付けましょう。
外側から内側の元テーブルを直接参照：外側クエリで WHERE orders.status = ... のように派生テーブルの内側テーブル名を使おうとするとエラーになります。外側からアクセスできるのは派生テーブルが返した列名（user_totals.total_amount など）のみです。
実務コラム：HAVING vs 派生テーブル どちらを使うべきか
シンプルな「集計後の絞り込み」だけなら HAVING SUM(amount) >= 15000 の方が短く書けます。一方、集計結果を別テーブルと JOIN する、複数の集計列を組み合わせてフィルタする、集計結果にさらに集計（二重集計）するような複雑なケースでは派生テーブル（またはCTE）が必要になります。実務では「まず HAVING を試し、複雑化したら派生テーブル/CTEに移行する」という判断が自然です。
4 / 5
問題を選択
QUESTION 5

EXISTS サブクエリ — 「存在するか」で絞り込む最も効率的なパターン

EXISTS相関SQ存在確認アクティブ検出
前提知識 ▼

WHERE EXISTS (サブクエリ) は、サブクエリが「1行でも結果を返す場合」にその行を残します。何を返すかは関係なく「行が存在するか」だけを確認するため、内側は SELECT 1 で十分です。

SELECT * FROM users u
WHERE EXISTS (
  SELECT 1                        -- 値は何でもよい。行の存在だけを確認
  FROM   orders o
  WHERE  o.user_id = u.user_id  -- 外側列を参照する「相関サブクエリ」
);
EXISTS の特徴：内側クエリが1行でも見つかった時点で即座に TRUE を返し、残りの探索をやめます（短絡評価）。大量データでも効率的に動作し、NULL の影響を受けない安全な書き方です。
問題 ▼

users テーブルから、status が 'completed' の注文が1件以上あるユーザーを取得してください。user_id, name, plan を user_id 昇順で返してください。

使用テーブル
▸ users
user_idnameplan
1田中 太郎premium
2佐藤 花子free
3鈴木 一郎premium
4山田 次郎standard
5伊藤 三郎free
▸ orders
order_iduser_idstatus
1011completed
1021completed
1032pending
1043completed
1053completed
1064cancelled
期待出力 ▼
user_idnameplan
1田中 太郎premium
3鈴木 一郎premium
模範解答コード ▼ 
SELECT
  user_id, name, plan
FROM   users u                    -- 外側クエリに u というエイリアス
WHERE  EXISTS (                   -- SQが1行以上返せば TRUE
  SELECT 1                       -- 値は何でもよい（1, *, 'x' どれも等価）
  FROM   orders o
  WHERE  o.user_id = u.user_id  -- 外側 u.user_id を参照する相関SQ
    AND  o.status  = 'completed' -- completedが1件でもあれば通過
)
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM users u                → 1行ずつ処理
  2. EXISTS(...)                 → ユーザーごとにサブクエリを実行
  3. SELECT user_id, name, plan  → 通過行を選択
  4. ORDER BY user_id            → user_id 昇順
  */
解説（テーブル変化・ポイント） ▼
SELECT user_id, name, plan FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.user_id AND o.status = 'completed' ) ORDER BY user_id;
LEGEND ▼
データ取得・読込対象
① FROM users ▼
FROM users uusersテーブル全5行を1行ずつ処理します。各行に対して EXISTS サブクエリを評価します。
1 / 3
user_idnameplan
1田中 太郎premium
2佐藤 花子free
3鈴木 一郎premium
4山田 次郎standard
5伊藤 三郎free
全 5行 読込
学習ポイント
EXISTS は「存在確認専用」の演算子：EXISTS はサブクエリが1行でも返すかどうかだけを調べます。SELECT 1 で書くのが慣例で「値を返すことに意味はない」ことを明示します。
外側クエリの列を参照する「相関サブクエリ」：EXISTS 内の o.user_id = u.user_id のように、外側クエリの列（u.user_id）を内側で参照する形式を相関サブクエリといいます。外側クエリが1行処理されるたびに内側が実行されます。
NULL に対して安全：EXISTS は NULL を特別扱いしません。サブクエリが行を返せば TRUE、返さなければ FALSE というシンプルな2値評価です。NOT IN の NULL 問題（Q3）が EXISTS では発生しません。
短絡評価による効率化：EXISTS は1行でも見つかった時点で即座に TRUE を返し探索を打ち切ります。「10万件の orders から特定ユーザーの注文を探す」場合も、最初の1件が見つかれば残り9万9999件を走査しません。
アンチパターン
SELECT * を EXISTS 内で使う：SELECT * と書いても動作しますが、EXISTS は値を一切使わないため無駄です。SELECT 1 または SELECT NULL が慣例であり、意図を明確にします。
EXISTS と IN の使い間違い：結合先の列も SELECT に出したい場合は EXISTS ではなく JOIN を使います。EXISTS はあくまで「存在確認のみ」で使い、結果セットに外側の列しか出てこない点を理解しましょう。
実務コラム：IN vs EXISTS パフォーマンスの考え方
「IN は全リストを先に生成するため遅く、EXISTS は行ごとに評価するため速い」という説明を見かけますが、現代の RDB オプティマイザは IN を内部的に EXISTS や JOIN に変換して最適化することが多く、一概には言えません。重要なのはインデックスが張られているか、テーブルの行数、結合の選択性です。チューニングは実行計画（EXPLAIN）で確認しましょう。
5 / 5
問題を選択
次のセット →