C
データベース 一覧
前へ2 / 6次へ
データベース/SQL 基礎/Lesson 02

SQL 基礎 — RDBMS・SELECT・WHERE・GROUP BY・JOIN

60分·theory
このチャプター
1/2

SQL 基礎 — RDBMS・SELECT・WHERE・GROUP BY・JOIN

🎯 このレッスンを読み終えたら

このレッスンをすべて読み終えると、以下の3つを自信を持って実行できるようになります。

  • ▸✅ SELECT・WHERE・GROUP BY・HAVING・ORDER BY 句の正確な順序
  • ▸✅ INNER JOIN と LEFT JOIN の違い＋ケース別の選び方
  • ▸✅ EXPLAIN で実行計画を読む方法（type と rows 列）

学習目標をチェックリストとして意識し、すべてに答えられるようになったらレッスンを閉じてください。

RDBMSのコア概念6つ

一言で: リレーショナルDB = テーブル + PK/FK + 正規化 + ACID + インデックス + SQL。50年の標準。

6つのコア概念

概念意味一行の例
テーブル・行・列データの2DグリッドCREATE TABLE users (id, name, email)
PK・FK一意識別＋リレーションorders.user_id REFERENCES users(id)
正規化 1・2・3NF冗長性の排除 → 異常の防止1NF（原子値）→ 2NF（部分従属）→ 3NF（推移従属）
ACIDトランザクションの4つの保証Atomic・Consistent・Isolated・Durable
インデックスB+Treeによる O(log n) 検索読み取り ↑、書き込みわずかに ↓
SQL 4分類言語の分類DDL・DML・DCL・TCL

正規化ステップのクイック解説

ステップルール違反例 → 解決策
1NF原子値のみ（配列・繰り返しグループ禁止）hobbies='読書,コーディング' → 別テーブルへ
2NF1NF ＋ 主キー全体に従属(注文ID, 商品ID) PK に 商品名 が従属 → 商品テーブルを分離
3NF2NF ＋ 非キー列間の従属なしuser_id → city → 郵便番号 → 郵便番号テーブルを分離
BCNFすべての決定項が候補キーほぼ同じ、さらに厳格

> 💡 実務では: 通常は3NFまで。パフォーマンスのために意図的な非正規化を行う場合もあります（参照頻度の高い箇所）。

SQL 4分類

分類用途コマンド
DDL (Definition)スキーマCREATE・ALTER・DROP・TRUNCATE
DML (Manipulation)データSELECT・INSERT・UPDATE・DELETE
DCL (Control)権限GRANT・REVOKE
TCL (Transaction)トランザクションBEGIN・COMMIT・ROLLBACK・SAVEPOINT

SELECT 7句の実行順序

記述順序 vs 実行順序

code
記述: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
実行: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
     ──────────────────────────────────────────────────────
     データを*準備*してから列を*選択*し、*ソート*＋*切り取り*

各句 — 役割とよくある落とし穴

句役割よくある落とし穴解決策
FROM・JOINテーブルの指定大きなテーブルを先に → メモリ ↑小さいテーブル・フィルターを先に
WHERE行フィルターWHERE UPPER(col)='X' → インデックス未使用WHERE col='X'
WHERE行フィルターWHERE col LIKE '%abc' → インデックス未使用LIKE 'abc%'
GROUP BYグループ化SELECT に グループキー・集計関数以外 の列 → エラーすべてグループキーまたは集計関数に
HAVINGグループフィルターWHERE との混同HAVING SUM>1000（集計後）
SELECT列の選択SELECT * → 不要なデータ転送必要な列のみ
ORDER BYソートインデックスなしだと全体ソート（O(n log n)）ソート列にインデックスを追加
LIMIT・OFFSETページングOFFSET 100000 → 非常に遅いキーセット方式（WHERE id > last_id）

暗記のコツ

  • ▸FWG・HSO・L — From-Where-Group / Having-Select-Order / Limit
  • ▸データを早めに絞る — WHERE が早いほど後の処理が軽くなる
  • ▸インデックスが使われない5つのパターン: 関数・LIKE の左側 %・型変換・NOT・OR

JOIN + GROUP BY パターン

JOIN 5種類の比較

種類意味使用場面
INNER両側が一致する行のみ注文＋決済（両方が存在するもの）
LEFT左側をすべて＋一致（なければNULL）すべてのユーザー＋注文（注文なしのユーザーも含む）
RIGHTLEFT の逆ほとんど使わない（LEFT で入れ替える）
FULL両側をすべて（UNION 効果）両側のユニーク行を参照
CROSS直積（M × N）通常は誤用。すべての組み合わせを生成

JOIN アンチパターン

アンチジョイン（片側にのみ存在する行）:

sql
SELECT u.* FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;          -- 一度も注文していないユーザー

自己結合（自己参照）:

sql
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

GROUP BY 6パターン

#パターン例
1単純グループGROUP BY user_id + COUNT(*)
2複数列GROUP BY user_id, DATE(created_at)
3HAVING（グループフィルター）HAVING COUNT(*) > 5
4時間単位GROUP BY DATE_TRUNC('day', created_at)
5CASE WHEN 集計SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END)
6ROLLUP・CUBE自動小計・合計（GROUP BY ROLLUP(year, month)）

WHERE vs HAVING

sql
SELECT user_id, COUNT(*) AS cnt
FROM orders
WHERE status = 'PAID'        -- 行フィルター（実行ステップ1）
GROUP BY user_id             -- グループ化
HAVING COUNT(*) > 5;         -- グループフィルター（集計後）

💾 実践1 — ユーザー別注文統計（LEFT JOIN + GROUP BY）

以下のSQLを実行して結果を確認してください。▶ 実行をクリックすると結果テーブルが展開されます。
📥 입력 DB 서버로 전송되는 쿼리
SELECT FROM: users length: 408 chars 
-- 入力: users テーブル + orders テーブル
-- 処理: LEFT JOIN で注文なしのユーザーも含める + GROUP BY でユーザーごとに集計
-- 出力: ユーザーごとの注文数と合計支払額（注文0件は0表示）

SELECT
  u.id,
  u.name,
  COUNT(o.id) AS order_count,           -- 注文件数
  COALESCE(SUM(o.amount), 0) AS total   -- 合計支払額（NULL → 0）
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
  AND o.status = 'PAID'                  -- 決済完了のみ
GROUP BY u.id, u.name
ORDER BY total DESC
LIMIT 5;
⚙️ 처리 단계 DB 엔진 내부 동작
📋
1. 파싱
SQL 문법 분석 → AST 생성
10ms
🧠
2. 옵티마이저
인덱스·조인 순서 최적화 (통계 기반)
25ms
⚙️
3. 실행
Storage Engine이 데이터 읽기
30ms
📊
4. 결과 반환
버퍼에 모아서 클라이언트에 전달
8ms
총 73ms
📤 출력
✓ 5 rows 4 cols 73ms
idnameorder_counttotal
42ホン・ギルドン15450000
41キム・チョルス12380000
37イ・ヨンヒ8215000
55パク・ミンス389000
12チェ・ジウォン00
💡 학습 포인트: 실제 DB는 파싱 → 옵티마이저 → 실행 → 반환 4단계를 거칩니다. 인덱스가 있으면 '옵티마이저' 단계가 수천배 빨라집니다.
🌐 본인 환경(PostgreSQL·MySQL 등)에서 직접 실행해 ms 차이를 체감해보세요.

💾 実践2 — 月別売上（DATE_TRUNC + HAVING）

時間集計 + HAVING フィルターのパターン。売上が100万円を超える月のみ表示します。
📥 입력 DB 서버로 전송되는 쿼리
SELECT FROM: orders length: 325 chars 
-- 入力: orders（created_at、total、status）
-- 処理: 月単位でグループ化 + 決済完了フィルター + 100万以上のみ
-- 出力: 月別注文数と売上

SELECT
  DATE_TRUNC('month', created_at)::DATE AS month,
  COUNT(*) AS orders,
  SUM(total) AS revenue,
  ROUND(AVG(total)) AS avg_order
FROM orders
WHERE status = 'PAID'
GROUP BY month
HAVING SUM(total) > 1000000
ORDER BY month;
⚙️ 처리 단계 DB 엔진 내부 동작
📋
1. 파싱
SQL 문법 분석 → AST 생성
10ms
🧠
2. 옵티마이저
인덱스·조인 순서 최적화 (통계 기반)
25ms
⚙️
3. 실행
Storage Engine이 데이터 읽기
27ms
📊
4. 결과 반환
버퍼에 모아서 클라이언트에 전달
8ms
총 70ms
📤 출력
✓ 4 rows 4 cols 70ms
monthordersrevenueavg_order
2025-01-01142182000012817
2025-02-01188245000013031
2025-03-01215312000014511
2025-04-01276418000015145
💡 학습 포인트: 실제 DB는 파싱 → 옵티마이저 → 실행 → 반환 4단계를 거칩니다. 인덱스가 있으면 '옵티마이저' 단계가 수천배 빨라집니다.
🌐 본인 환경(PostgreSQL·MySQL 등)에서 직접 실행해 ms 차이를 체감해보세요.

💾 実践3 — ウィンドウ関数（ユーザー別直近注文 Top 3）

応用パターン — ROW_NUMBER + PARTITION BY を使って、ユーザーごとに上位N件を抽出します。
📥 입력 DB 서버로 전송되는 쿼리
SELECT FROM: orders length: 326 chars 
-- 入力: orders
-- 処理: PARTITION BY user_id でユーザーごとにソート → 上位3件
-- 出力: 各ユーザーの最新注文3件

WITH ranked AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC
    ) AS rn
  FROM orders o
)
SELECT user_id, id AS order_id, total, created_at
FROM ranked
WHERE rn <= 3
ORDER BY user_id, rn;
⚙️ 처리 단계 DB 엔진 내부 동작
📋
1. 파싱
SQL 문법 분석 → AST 생성
10ms
🧠
2. 옵티마이저
인덱스·조인 순서 최적화 (통계 기반)
25ms
⚙️
3. 실행
Storage Engine이 데이터 읽기
30ms
📊
4. 결과 반환
버퍼에 모아서 클라이언트에 전달
8ms
총 73ms
📤 출력
✓ 5 rows 4 cols 73ms
user_idorder_idtotalcreated_at
421832450002025-04-25
421801320002025-04-22
421789280002025-04-19
411820180002025-04-24
411798550002025-04-21
💡 학습 포인트: 실제 DB는 파싱 → 옵티마이저 → 실행 → 반환 4단계를 거칩니다. 인덱스가 있으면 '옵티마이저' 단계가 수천배 빨라집니다.
🌐 본인 환경(PostgreSQL·MySQL 등)에서 직접 실행해 ms 차이를 체감해보세요.
💻 📌 実践SQLパターン集（コピー用チートシート） 
-- ============================================
-- よく使うSQLパターン8選 (PostgreSQL基準)
-- ============================================

-- 1) UPSERT (存在すれば更新、なければINSERT)
INSERT INTO users (email, name) VALUES ('[email protected]', 'ホン・ギルドン')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- 2) keyset pagination (大きなOFFSETの代わりに)
-- ❌ 遅い: SELECT * FROM posts ORDER BY id DESC OFFSET 100000 LIMIT 10;
-- ✅ 速い:
SELECT * FROM posts WHERE id < :last_id ORDER BY id DESC LIMIT 10;

-- 3) 重複排除 + 最新1件のみ
SELECT DISTINCT ON (user_id) user_id, id, created_at
FROM orders ORDER BY user_id, created_at DESC;

-- 4) JSONカラム検索 (Postgres jsonb)
SELECT id, metadata->>'theme' AS theme
FROM users WHERE metadata @> '{"premium": true}';

-- 5) 全文検索 (Full-text)
SELECT * FROM articles
WHERE to_tsvector('japanese', title || ' ' || body) @@ plainto_tsquery('Spring Boot');

-- 6) 安全なカウント (正確 vs 推定)
SELECT COUNT(*) FROM huge_table;                     -- 正確、遅い
SELECT reltuples FROM pg_class WHERE relname='huge_table';  -- 推定、即時

-- 7) Window関数 — 累積合計・移動平均
SELECT date, sales,
       SUM(sales) OVER (ORDER BY date) AS cumulative,
       AVG(sales) OVER (ORDER BY date ROWS 6 PRECEDING) AS ma7
FROM daily_sales;

-- 8) Recursive CTE — 階層構造 (組織図・カテゴリツリー)
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org ORDER BY depth, name;

EXPLAIN — クエリ実行計画の確認

EXPLAIN が必要な理由

同じ結果を返すクエリでも、DBが内部的にどう実行するかによって速度が数十〜数百倍変わることがあります。EXPLAIN は MySQL オプティマイザーの判断を確認するためのツールです。

使い方

sql
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

結果:

code
+----+--------+-------+------+---------------+---------+---------+-------+------+
| id | select | table | type | possible_keys | key     | rows    | Extra |
+----+--------+-------+------+---------------+---------+---------+-------+------+
|  1 | SIMPLE | users | ref  | idx_email     | idx_email | 1     | NULL  |
+----+--------+-------+------+---------------+---------+---------+-------+------+

最も重要な列 — type と rows

type のランク — 良い順

1. const / system — 1件確定（PKで検索）。最良。
2. eq_ref — JOINでPK・UNIQUEを使用。非常に良い。
3. ref — インデックス使用。良い。
4. range — 範囲インデックス（WHERE age > 20）。許容範囲。
5. index — インデックス全体スキャン。悪い。
6. ALL — フルテーブルスキャン。最悪 — インデックスなしの状態。

type 列に ALL が表示されたら、インデックスを作成するかクエリを修正してください。

rows — 検査する行数の見積もり

100万行のテーブルで rows: 1000000 → フルスキャン。rows: 5 → インデックスヒット。rows が小さいほど高速。

EXPLAIN ANALYZE — 実際の実行時間まで確認（MySQL 8+）

sql
EXPLAIN ANALYZE SELECT ...;

実際にクエリを実行しながら、各ステップの所要時間を出力します。遅いクエリのデバッグに欠かせないツールです。

実務ワークフロー

1. スロークエリログ（slow_query_log）から1秒以上かかるクエリを収集
2. 各クエリに EXPLAIN を実行
3. type=ALL または rows が大きいクエリ → インデックス追加を検討
4. EXPLAIN ANALYZE で改善前後を比較

🤖 AIへのリクエスト例

このレッスンの概念を知っていれば、AIに具体的な指示を出せます。漠然とした「直して」ではなく、語彙を持ったリクエスト — それがトークン節約の出発点です。

  • ▸「users・orders・products の3テーブル設計をFK・INDEXまで含めて作って」
  • ▸「直近7日間に登録したユーザー数を日別に集計するクエリを書いて」
  • ▸「このクエリにEXPLAINを付けて実行計画を解釈して」

なぜトークンが減るのか

概念を知らないと、AIの回答を受け取っても「それって何ですか？」と再度質問が必要になります。その「再質問」がトークンを消費します。概念を一度身につければ、会話が一度で完結します。

先に読むとよい概念: データベース
次のおすすめ: SQL 実習 — テーブル作成からデータ挿入・照会まで
前へ一覧次へ
SQL 基礎 — RDBMS・SELECT・WHERE・GROUP BY・JOIN - データベース