SQLの基礎（結合・集約・サブクエリ）｜データエンジニアリング

🎓 レベル：基礎　|　重要度：A（必須）

📎 前提：リレーショナルモデルと正規化　|　関連：ウィンドウ関数・インデックスと実行計画

要点（BLUF）

SQLは集合に対する演算。1行ずつのループではなく「テーブル（集合）から条件で絞り、繋ぎ、まとめる」を宣言的に書く。
三本柱：JOIN（テーブルを繋ぐ）／GROUP BYによる集約（行をまとめて集計）／サブクエリ（クエリを入れ子にする）。
書く順（SELECT…FROM…）と**実行される論理順（FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY）**は違う。これを知ると「WHEREで集約関数が使えない」理由が腑に落ちる。

概念 ── 宣言的な集合演算

手続き型（Python等）は「どう計算するか」を書きますが、SQLは「どんな結果が欲しいか」だけを書きます。どう実行するか（走査順・索引利用）はDBのクエリオプティマイザが決めます（→ インデックスと実行計画）。だからこそ、書き手は集合のイメージを持つことが大事です。

仕組み① ── JOINと集約

2つのテーブルを外部キーで繋ぎ（JOIN）、地域ごとにまとめて（GROUP BY）売上を集計します。

SELECT c.region, COUNT(*) AS n_orders, SUM(o.amount) AS sales
FROM orders o
JOIN customers c ON o.cust_id = c.cust_id
GROUP BY c.region
ORDER BY sales DESC;

実行結果（実機・SQLite）:

('East', 5, 6900)
('West', 3, 2100)

JOIN ... ON で行を対応づけ、GROUP BY c.region で地域ごとに畳み込み、SUM/COUNTで集計します。SELECTに書ける列は「GROUP BYした列」か「集約関数」だけ——これは“地域ごとに1行”に畳んだ後では、個々の注文IDが一意に決まらないからです。

仕組み② ── サブクエリ

クエリの結果を別のクエリの中で使うのがサブクエリです。「全体平均より高い注文」を出します。

SELECT order_id, amount FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders)
ORDER BY amount DESC;

実行結果（実機・SQLite）:

(4, 3000)
(8, 1500)
(1, 1200)

内側 (SELECT AVG(amount) ...) が先に1つの値（平均）を返し、外側がそれと比較します。サブクエリにはスカラ（1値）・行・相関（外側の各行に依存）など種類があり、可読性が落ちるなら**CTE（WITH句）**で名前を付けて分けると読みやすくなります。

設計の勘所 ── 論理的な実行順序

flowchart LR
    F["FROM/JOIN（集合を作る）"] --> W["WHERE（行を絞る）"]
    W --> G["GROUP BY（畳む）"]
    G --> H["HAVING（畳んだ後で絞る）"]
    H --> S["SELECT（列を選ぶ・計算）"]
    S --> O["ORDER BY（並べる）"]

WHEREは畳む前の行に効き、HAVINGは畳んだ後の集計値に効く。だから「合計が1000超のグループだけ」はHAVING SUM(amount)>1000であってWHEREでは書けません。SELECTの別名（AS sales）をWHEREで使えないのも、SELECTが後だからです。

なぜそうするか ── 集合で考える

なぜループでなく集合演算なのか。最適化をDBに任せられるからです。「どう繋ぐか」を書かなければ、DBは索引・結合アルゴリズム（ネストループ／ハッシュ結合）・並列を状況に応じて選べます。書き手が手続きを固定すると、この最適化の余地を奪ってしまう。SQLの宣言性は、性能のための余白でもあります。

⚠️ よくある落とし穴

JOIN条件（ON）を書き忘れる → 直積（クロスジョイン）で行数が爆発。必ず結合キーを指定。
外部キーにNULLや欠損 → 内部結合（INNER JOIN）で行が静かに消える。残したいならLEFT JOIN。
GROUP BY漏れ → 多くのDBでエラー、SQLiteは曖昧な値を返す。SELECTの非集約列は必ずGROUP BYに。
COUNT(*)とCOUNT(col)を混同 → 後者はNULLを数えない。

対応ラボ

data-engineering-study/labs/03_sql_optimization.py（PYTHONIOENCODING=utf-8 で実行・上記出力を確認済み）。

確認できること：JOIN集約、サブクエリ、ウィンドウ関数、実行計画、ファイル形式