SQLite | ゼロからはじめるSQL、30日で習得するSQLite：データ操作・設計 － Day21 サブクエリ①

SQL SQLite
スポンサーリンク

Day21 前半

「SELECT の“1列”として、もう1回 SELECT を動かす」という発想を持つ

ここまであなたは、
1つの SELECT でテーブルから行を取り出す
複数テーブルを JOIN して情報をくっつける
GROUP BY でまとめて集計する

といった「1本の SELECT をどう書くか」をずっと鍛えてきました。

Day21 からは、少し視点を変えます。

テーマは サブクエリ（subquery）。
その中でもまずは、
SELECT 句の中に書くサブクエリ に絞ります。

イメージとしては、

「SELECT の1列を、“別の SELECT の結果”で埋める」

というものです。

1本の SELECT の中に、
小さな SELECT を“埋め込む”感じだと思ってください。

何をしたいときに使うのか

「1行ごとに“計算した値”や“集計結果”をくっつけたい」

まずは、やりたいことのイメージからいきます。

次のようなテーブルがあるとします。

users
id | name
---+-----------
1  | 山田太郎
2  | 佐藤花子
3  | 鈴木一郎

orders
id | user_id | amount
---+---------+-------
1  | 1       | 1200
2  | 1       | 3000
3  | 2       | 500
SQL

ここで欲しいのは、こんな一覧です。

id | name       | order_count
---+------------+------------
1  | 山田太郎   | 2
2  | 佐藤花子   | 1
3  | 鈴木一郎   | 0

つまり、

ユーザーごとに
「この人が何件注文しているか」を数えた値を
1行ごとにくっつけたい

という状況です。

JOIN でも書けますが、
ここではあえて SELECT 内サブクエリ でやってみます。

SELECT 内サブクエリの基本形

「外側の1行に対して、内側で“小さな SELECT”を走らせる」

先ほどの要件を、サブクエリで書くとこうなります。

SELECT
  u.id,
  u.name,
  (
    SELECT COUNT(*)
    FROM orders o
    WHERE o.user_id = u.id
  ) AS order_count
FROM users u;
SQL

ここで注目してほしいのは、3つ目の列です。

(
  SELECT COUNT(*)
  FROM orders o
  WHERE o.user_id = u.id
) AS order_count
SQL

この部分が SELECT 内のサブクエリ です。

やっていることを日本語にすると、

「今処理している users の1行（u）について、
orders から user_id が u.id の行だけを数える」

です。

外側の SELECT は users を1行ずつ見ていきます。
そのたびに、内側の SELECT が

「このユーザーの注文は何件？」

と聞きに行き、その結果（1つの数値）を
order_count 列としてくっつけています。

つまり、

外側の1行ごとに、
内側で“小さな SELECT”が1回動いている

というイメージです。

「相関サブクエリ」という言葉

「内側の SELECT が、外側の行に“紐づいている”」

今のように、

サブクエリの中で、外側のテーブル（u.id）を参照している

タイプのサブクエリを、
相関サブクエリ（correlated subquery） と呼びます。

相関サブクエリのポイントは、

外側の行が変わるたびに、
内側の SELECT の条件も変わる

ということです。

さっきの例だと、

users の1行目（id=1）を処理するとき
内側は WHERE o.user_id = 1 で COUNT

users の2行目（id=2）を処理するとき
内側は WHERE o.user_id = 2 で COUNT

というふうに、
「行ごとに違う SELECT が走る」 と思ってください。

この「行ごとに違う計算をして、その結果を列としてくっつける」
というのが、SELECT 内サブクエリの一番おいしいところです。

JOIN＋GROUP BY との違いを、あえて意識してみる

「全体を一気に集計するか、1行ずつ小さく集計するか」

同じ結果は、JOIN と GROUP BY でも書けます。

SELECT
  u.id,
  u.name,
  COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o
  ON u.id = o.user_id
GROUP BY u.id, u.name;
SQL

こちらは、

users と orders を一度にくっつけて
全体をまとめて GROUP BY で集計する

というスタイルです。

一方、SELECT 内サブクエリは、

users を1行ずつ見ながら
その行に対してだけ orders を集計する

というスタイルです。

どちらも正しい書き方ですが、
頭の中のイメージが少し違います。

サブクエリは、

「この1行に対して、ちょっとだけ追加計算したい」

というときに、
コードとして“まとまり”を作りやすい書き方です。

もう一歩：最新の注文金額をくっつける例

「ORDER BY と LIMIT 1 を組み合わせたサブクエリ」

同じテーブルで、別の要件を考えてみます。

「ユーザー一覧に、“その人の最後の注文金額”を出したい」

欲しい結果はこうです。

id | name       | last_amount
---+------------+------------
1  | 山田太郎   | 3000
2  | 佐藤花子   | 500
3  | 鈴木一郎   | NULL

これも SELECT 内サブクエリで書けます。

SELECT
  u.id,
  u.name,
  (
    SELECT o.amount
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY o.id DESC
    LIMIT 1
  ) AS last_amount
FROM users u;
SQL

ここでやっていることは、

外側の users の1行ごとに、
その user_id の注文を id 降順で並べて、
一番上の amount を1件だけ取る

という処理です。

COUNT のときと同じく、
サブクエリの中で u.id を参照しているので、
行ごとに条件が変わります。

このように、

集計関数（COUNT, MAX など）
ORDER BY … LIMIT 1

と組み合わせることで、
「1行ごとの“代表値”」を柔軟にくっつけられます。

SELECT 内サブクエリの大事な制約

「1行1列だけ返すように書く」

ここがとても重要なので、はっきり言います。

SELECT 内サブクエリは、
「1行1列だけ返す」ように書かなければいけません。

COUNT(*) は必ず1行1列です。
ORDER BY … LIMIT 1 も、1行1列です。

だから、さっきの例はどちらも OK です。

逆に、こう書くと危険です。

SELECT
  u.id,
  u.name,
  (
    SELECT o.amount
    FROM orders o
    WHERE o.user_id = u.id
  ) AS some_amount
FROM users u;
SQL

このサブクエリは、
同じ user_id の注文が複数あれば、複数行返してしまいます。

1つのセルに複数行は入れられないので、
SQLite はエラーを出します。

「1セルに1つの値」という、
第1正規形のルールを思い出してください。

SELECT 内サブクエリは、
そのルールを守るためにも、

集計関数で1つにまとめる
ORDER BY ＋ LIMIT 1 で1行に絞る

といった工夫が必須です。

セキュリティの視点から見た“行ごとのサブクエリ”

サブクエリ自体は、
直接セキュリティ機能になるわけではありません。

ただ、

「1行ごとに別の条件で問い合わせる」

という性質は、
権限やログと組み合わせると便利です。

例えば、

ユーザー一覧に「最後にログインした日時」を出したい
ログイン履歴は別テーブルにある

というとき、

SELECT 内サブクエリで
「そのユーザーのログイン履歴のうち、最新1件だけ」を取る

という書き方ができます。

ログイン履歴テーブルへのアクセス権限を
アプリ側でしっかり制御しておけば、

「必要な情報だけを、必要な形でくっつける」

という安全な設計がしやすくなります。

Day21 前半のまとめ

SELECT 内サブクエリは、「SELECT の1列として、別の SELECT の結果（1行1列）をくっつける」ための仕組み。
相関サブクエリでは、内側の SELECT が外側の行（u.id など）に紐づいており、行ごとに条件が変わる“小さな SELECT”が動く。
ユーザー一覧に「注文数」や「最新の注文金額」など、“1行ごとの追加情報”を計算して付けるのに向いている。
サブクエリは必ず「1行1列だけ返す」ように、集計関数や ORDER BY ＋ LIMIT 1 を組み合わせて書く必要がある。
JOIN＋GROUP BY でも書けるケースは多く、サブクエリは「1行ごとの追加計算」として、読みやすさや意図の明確さで選び分けるイメージを持つとよい。

後半では、
相関サブクエリが遅くなりやすい理由
JOIN や集約で書き換えた方がよいパターン
サブクエリ・ビュー・WITH句（CTE）の使い分けの入り口

まで踏み込んで、「知っている」から「使いこなす」へ一段上げていきます。

次へ
12
スポンサーリンク
タイトルとURLをコピーしました