SQL 入門ガイド

目次

基礎編

1. SQL を組む際の順序とコツ
   • SQL の論理的な処理順序
   • SQL を組む際のコツ
   • 実際の記述順序
2. JOIN の種類と使い分け
   • 内部結合（INNER JOIN）
   • 外部結合（OUTER JOIN）
   • その他の結合
3. 基本的な SQL 構文
   • WHERE - 条件による絞り込み
   • GROUP BY - グループ化
   • HAVING - グループ化後の絞り込み
   • SELECT - 取得する列の指定
   • DISTINCT - 重複行の削除
   • ORDER BY - 並び替え
   • LIMIT/OFFSET - 行数制限
4. 演算子とデータ処理
   • IN 演算子
   • NULL 値の処理
5. 集計関数
6. 集計関数の活用例
7. ビュー
8. サブクエリ
9. EXISTS 演算子
10. CASE 式
    • SELECT 句での CASE 式の役割
    • WHERE 句での CASE 式の役割
11. セット演算子
    • UNION
    • INTERSECT と EXCEPT
    • UNION の代用方法が有効になるパターン
12. ウィンドウ関数
13. データ操作
    • INSERT - データ追加
    • UPDATE - データ更新
    • DELETE - データ削除
    • MERGE - 複合処理
    • USING の使い方

応用編

1. パフォーマンスとベストプラクティス
   • GROUP BY での注意点
   • CASE 式と GROUP BY の活用
   • サブクエリの問題点と対策
   • サブクエリのパフォーマンス改善策とウィンドウ関数活用
   • サブクエリがパフォーマンスが良いパターンと SQL 例
   • 相関サブクエリとは
2. SQL の処理内容について
   • 処理内容について ①
   • 処理内容について ②
   • 処理内容について ③
   • FOLLOWING
3. SQL 関数の詳細
   • COALESCE 関数の使い方
   • SIGN 関数について
   • INSERT SELECT の使い方とメリット
   • Group 化する際のテクニック
4. インデックスとパフォーマンス
   • インデックスが効かない場合の対策
   • データマートにおけるインデックス効率化対策
   • インデックスオンリースキャン対策と運用注意点
5. プログラミング概念
   • 手続型と宣言型の違い
   • パラレル実行の基本的な仕組み
6. JSON データ処理
   • JSON_CONTAINS 関数の詳細な使い方
   • JSON_TABLE の詳細な仕組みと実践活用
   • MySQL の JSON 関連関数の解説
7. データベース内部構造
   • TEMP 落ちとは
   • データベースの結合アルゴリズムの 3 種類
   • 実行計画をユーザーが手動で制御することのリスク
8. 実践的な課題
   • サブクエリパラノイア
   • データマート

SQL を組む際の順序とコツ

SQL を効率的に記述するためには、論理的な処理順序を理解することが重要です。SQL の実際の処理順序に沿って考えることで、クエリの構築が整理しやすくなります。

SQL の論理的な処理順序

SQL の処理は以下の順序で実行されます：

1. FROM - データを取得するテーブルを指定
2. JOIN - 他のテーブルとの結合
3. WHERE - 条件に基づいて行をフィルタリング
4. GROUP BY - 指定した列でグループ化 F
5. HAVING - グループ化後の条件でフィルタリング
6. SELECT - 取得する列を指定
7. DISTINCT - 重複行の削除
8. ORDER BY - 結果のソート
9. LIMIT/OFFSET - 結果の行数制限

この順序は実際の SQL の記述順序とは異なりますが、データベースエンジンがクエリを処理する順序です。

SQL を組む際のコツ

1. 考え方の順序に従って記述する

SQL を書く際は、以下のアプローチに沿って考えると整理しやすいです：

• まず欲しいデータ（登場人物）とその関係を書く（FROM→WHERE→JOIN→ON→WHERE）
• 必要な粒度に揃える（GROUP BY）、さらに絞り込む（HAVING）
• 欲しいデータが欲しい粒度で揃ったので、SELECT 句で必要な項目を指定する

2. WHERE 句と HAVING 句の使い分け

• WHERE 句は集約前のデータに対するフィルタリング
• HAVING 句は集約後のデータに対するフィルタリング

3. エイリアスの使用に注意

WHERE 句では SELECT で定義したエイリアスは使用できません。これは処理順序で WHERE が SELECT より先に実行されるためです。エイリアスを条件に使用したい場合は：

• サブクエリを使用する
• HAVING 句を使用する（集約関数と一緒に使うことが一般的）

4. 集約関数の使用

集約関数（SUM, COUNT, AVG など）は WHERE 句では使用できません。代わりに HAVING 句で使用します。

実際の記述順序

実際に SQL を記述する順序は以下のようになります：

SELECT [DISTINCT] カラム名
FROM テーブル名
JOIN 結合テーブル ON 結合条件
WHERE 条件
GROUP BY グループ化するカラム
HAVING グループ化後の条件
ORDER BY ソートするカラム
LIMIT 行数 OFFSET 開始位置

SQL を書く際は、データの流れを意識しながら、必要なデータをどのように取得・加工するかを考えることが重要です。処理順序を理解することで、より効率的で正確なクエリを作成できるようになります。

JOIN の種類

内部結合（INNER JOIN）

• 特徴: 両テーブルで結合条件が一致する行のみ取得
• 用途: 関連データが確実に存在する場合の結合
• 例:

  SELECT users.name, orders.product
  FROM users
  INNER JOIN orders ON users.id = orders.user_id;
  

外部結合（OUTER JOIN）

左外部結合（LEFT OUTER JOIN）

• 特徴: 左テーブルの全行＋右テーブルの一致行（不一致は NULL）
• 例:

  SELECT users.name, orders.product
  FROM users
  LEFT OUTER JOIN orders ON users.id = orders.user_id;
  

右外部結合（RIGHT OUTER JOIN）

• 特徴: 右テーブルの全行＋左テーブルの一致行（不一致は NULL）
• 例:

  SELECT users.name, orders.product
  FROM users
  RIGHT OUTER JOIN orders ON users.id = orders.user_id;
  

完全外部結合（FULL OUTER JOIN）

• 特徴: 両テーブルの全行を取得（不一致は NULL）
• 注意: MySQL では未対応
• 例:

  SELECT users.name, orders.product
  FROM users
  FULL OUTER JOIN orders ON users.id = orders.user_id;
  

クロス結合（CROSS JOIN）

• 特徴: 全ての組み合わせを生成（直積）
• 用途: 組み合わせパターンの生成
• 例:

  SELECT colors.name, sizes.name
  FROM colors
  CROSS JOIN sizes;
  

自己結合（SELF JOIN）

• 特徴: 同じテーブルを別名で結合
• 用途: 階層データや比較分析
• 例（従業員と上司の関係）:

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

等値結合（Equi Join） vs 非等値結合（Non-Equi Join）

非等値結合の例（給与ランク判定）:

SELECT e.name, s.grade
FROM employees e
JOIN salary_grades s
  ON e.salary BETWEEN s.min_salary AND s.max_salary;

自然結合（NATURAL JOIN）

• 特徴: 同名カラムで自動結合
• 注意: 予期せぬ結合やメンテナンス性の問題あり
• 例:

  SELECT name, product
  FROM users
  NATURAL JOIN orders;
  

  → users.idとorders.user_idなど同名カラムがあれば自動結合

結合の比較表

実践的な組み合わせ例

非等値結合＋自己結合（給与比較）:

SELECT
  e1.name AS employee,
  e2.name AS higher_paid_colleague
FROM employees e1
JOIN employees e2
  ON e1.department = e2.department
  AND e1.salary < e2.salary;

クロス結合＋条件付き集計（商品 × 地域の売上分析）:

SELECT
  p.product_name,
  r.region_name,
  COALESCE(SUM(s.sales), 0) AS total_sales
FROM products p
CROSS JOIN regions r
LEFT JOIN sales s
  ON p.product_id = s.product_id
  AND r.region_id = s.region_id
GROUP BY p.product_name, r.region_name;

基本的な SQL 構文

WHERE - 条件による絞り込み（AND, OR, IN）

SELECT * FROM users
WHERE (city = '東京' OR city = '大阪')
  AND age IN (20, 25, 30);

都市が「東京」または「大阪」かつ、年齢が 20・25・30 歳のいずれかのユーザーを取得します。

GROUP BY - グループ化

SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;

都市ごとにユーザー数を集計します。

HAVING - グループ化後の絞り込み

SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 10;

ユーザー数が 10 人を超える都市のみ抽出します。

SELECT - 取得する列の指定

SELECT name, email FROM users;

users テーブルから name と email だけを取得します。

DISTINCT - 重複行の削除

SELECT DISTINCT city FROM users;

都市名の重複を除いて一覧化します。

ORDER BY - 並び替え

SELECT * FROM users
ORDER BY age DESC, name ASC;

年齢の降順、同じ年齢なら名前の昇順で並べます。

LIMIT/OFFSET - 行数制限

SELECT * FROM users
ORDER BY id
LIMIT 5 OFFSET 10;

id 順で 11 行目から 5 件だけ取得します。

演算子とデータ処理

IN 演算子

複数の OR 条件を簡潔に記述できる便利な構文です。
たとえば、「あるカラムの値が A または B または C に一致するデータを抽出したい」とき、通常は以下のように OR を使って書きます。

SELECT * FROM users
WHERE city = '東京' OR city = '大阪' OR city = '名古屋';

このように OR を繰り返すと、条件が増えるたびに SQL 文が長くなり、可読性も低下します。

そこで IN を使うと、下記のようにシンプルにまとめられます。

SELECT * FROM users
WHERE city IN ('東京', '大阪', '名古屋');

IN の後にカンマ区切りで値を並べるだけで、「いずれかに一致する」という OR 条件を一文で表現できます。

SQL で NULL 値を扱う際は、以下のように「IS NULL」演算子や「ISNULL」「COALESCE」関数を使い分けます。

NULL 値の処理

IS NULL 演算子

カラムの値が NULL かどうかを判定するには「IS NULL」を使います。

SELECT * FROM users WHERE name IS NULL;

このクエリは、name が NULL のレコードを抽出します。

NULL の判定には「= NULL」や「<> NULL」は使えないので注意してください。

ISNULL 関数

ISNULL 関数は、値が NULL の場合に指定した値で置き換えます（主に SQL Server や Access で利用）。

SELECT ISNULL(name, '未登録') FROM users;

name が NULL なら「未登録」と表示します。

COALESCE 関数

COALESCE 関数は、複数の引数の中から最初に NULL でない値を返します。多くのデータベースで利用できます。

SELECT COALESCE(phone_home, phone_mobile, '未登録') AS phone FROM users;

phone_home が NULL なら phone_mobile、それも NULL なら「未登録」となります。

集計関数

SQL の集計関数（集約関数）は、テーブルの複数行に対して合計や平均、最大・最小値、件数などを計算し、1 つの結果として返す関数です。主な集計関数とその使い方を解説します。

1. COUNT（カウント：件数を数える）

• COUNT(*)
  テーブル内の全行数（NULL も含む）を返します。
• COUNT(列名)
  指定した列の値が NULL でない行数を返します。
• COUNT(1)
  COUNT(*)とほぼ同じく、全行数を返します。
  ※COUNT(1)は、各行ごとに「1」という値をカウントするため、NULL の有無に関係なく全行がカウントされます。

SELECT COUNT(*) FROM users;      -- 全行数
SELECT COUNT(email) FROM users;  -- emailがNULLでない行数
SELECT COUNT(1) FROM users;      -- 全行数（COUNT(*)と同じ結果）

2. SUM（サム：合計値を求める）

• 指定した列（数値型）の合計値を返します。NULL 値は集計対象外です。

SELECT SUM(price) FROM items;

3. AVG（アベレージ：平均値を求める）

• 指定した列（数値型）の平均値を返します。NULL 値は集計対象外です。

SELECT AVG(score) FROM tests;

4. MAX（マックス：最大値を求める）

• 指定した列の中で最大の値を返します。

SELECT MAX(age) FROM users;

5. MIN（ミニマム：最小値を求める）

• 指定した列の中で最小の値を返します。

SELECT MIN(age) FROM users;

集計関数の活用例

• WHERE 句と組み合わせて、集計対象を絞り込むことができます。

SELECT COUNT(*) FROM users WHERE city = '東京';

• GROUP BY 句と組み合わせると、指定した列ごとに集計できます。

SELECT city, COUNT(*) FROM users GROUP BY city;

• HAVING 句を使うと、グループ化後の集計結果に条件を付与できます。

SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 10;

ビュー

ビューとは

ビューは、SELECT 文の結果を仮想的なテーブル（導出表）として定義し、保存しておく仕組みです。実際のデータは持たず、元となるテーブルやクエリの結果を参照します。複雑なクエリの簡略化や、特定のデータだけをユーザーに見せるセキュリティ対策、再利用性の向上などに役立ちます。

基本構文

CREATE VIEW ビュー名 AS
SELECT 列名1, 列名2, ...
FROM テーブル名
WHERE 条件;

具体例

1. テーブルから特定のカラムだけを抽出

CREATE VIEW productlist AS
SELECT id, name FROM product;

この例では、product テーブルの id と name だけを持つビューを作成しています。

2. 条件付きでビューを作成

CREATE VIEW SalesEmployees AS
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
WHERE Department = 'Sales';

Sales 部門の従業員だけを抽出するビューです。

3. 集計や計算結果を含むビュー

CREATE VIEW sales AS
SELECT name, SUM(num * price) AS total
FROM report
GROUP BY name;

商品ごとの売上合計を集計したビューです。

4. 複数テーブルの結合を含むビュー

CREATE VIEW user_orders AS
SELECT users.id, users.name, orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id;

users テーブルと orders テーブルを結合したビューです。

ビューのカラム名を指定する

ビュー独自のカラム名を指定したい場合は、以下のように記述します。

CREATE VIEW view_name (col1, col2, ...) AS
SELECT ... FROM ...;

ビューの利用

作成したビューは、通常のテーブルのように SELECT 文で参照できます。

SELECT * FROM productlist;

注意点

• ビューは実データを持たず、定義のみを保存します。
• ビュー名はデータベース内で一意でなければなりません。
• ビューの定義に使う SELECT 文は 1 つのみです。
• ビューの作成には適切な権限が必要です。

サブクエリ

サブクエリ（副問合せ）は、SQL 文の中にさらに別の SQL 文（SELECT 文など）を入れ子で記述できる強力な機能です。サブクエリを使うことで、複雑なデータ抽出や集計、条件指定をシンプルに記述できます。主な使い方とその便利な活用例を紹介します。
なお SQL はサブクエリから実行されます

1. WHERE 句での絞り込み

サブクエリは、WHERE 句で「他のテーブルや集計結果を条件にしたい」ときに便利です。

SELECT customer_name, sales_amount
FROM customers
WHERE sales_amount > (SELECT AVG(sales_amount) FROM customers);

この例では「平均売上金額より多い顧客」を抽出しています。サブクエリで平均値を計算し、その値を WHERE 句の条件に利用しています。

2. IN や EXISTS との組み合わせ

IN や EXISTS とサブクエリを組み合わせることで、複数の値の存在チェックや関連データの抽出が簡単にできます。

SELECT e.emp_no, e.first_name, e.last_name
FROM employees e
WHERE e.emp_no IN (
  SELECT dm.emp_no
  FROM dept_manager dm
);

この例では「マネージャー経験がある従業員」を抽出しています。サブクエリでマネージャーの社員番号リストを取得し、IN で絞り込んでいます。

3. SELECT 句での値の取得

SELECT 句でサブクエリを使うことで、各行ごとに追加情報を取得できます（相関サブクエリ）。

SELECT E.ID, E.NAME,
  (SELECT NAME FROM DEPARTMENT WHERE ID = E.ID) AS DEP_NAME
FROM EMPLOYEES E;

この例では、各従業員が所属する部署名をサブクエリで取得しています。結合（JOIN）を使わずに、1 行ずつ関連情報を引き出せます。

4. FROM 句でのテーブル化

サブクエリを FROM 句に使うと、サブクエリの結果を仮想テーブル（インラインビュー）として利用できます。

SELECT sub.department, COUNT(*) AS emp_count
FROM (SELECT department FROM employees WHERE age > 30) sub
GROUP BY sub.department;

このように、サブクエリの集計結果やフィルタリング結果をさらに外側のクエリで利用できます。

5. HAVING 句での集計条件指定

HAVING 句の中でもサブクエリが使えます。これにより、グループ化した集計値に対して他の集計結果を条件として指定できます。

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > (SELECT AVG(dept_count) FROM (SELECT COUNT(*) AS dept_count FROM employees GROUP BY department) t);

この例では、「部署ごとの人数が、全部署の平均人数より多い部署」を抽出しています。

6. UPDATE や INSERT でも活用

サブクエリは UPDATE や INSERT 文の値指定にも使えます。例えば、他テーブルの値で一括更新・追加したい場合に便利です。

UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees);

全従業員の給与を平均値で更新する例です。

サブクエリのメリット

• 複雑な条件や集計をシンプルに記述できる
• JOIN を使わずに関連データを取得できる場合がある
• 集約関数や複数テーブルの比較など、結合だけでは難しい処理も可能

注意点

• サブクエリは複雑になるとパフォーマンスが低下することがあるため、必要に応じて JOIN や WITH 句（共通テーブル式）も検討しましょう。

EXISTS 演算子

基本概要

EXISTS は、指定したサブクエリで 1 件以上のレコードが存在するかどうかを判定するための SQL の述語です。
サブクエリが 1 行でも結果を返せば TRUE、1 件も返さなければ FALSE となり、主クエリの WHERE 句などで条件分岐に利用できます。
主に「他テーブルに関連データが存在する場合のみ抽出したい」ときに使います。

基本構文

SELECT 列名
FROM テーブルA
WHERE EXISTS (
  サブクエリ
);

• サブクエリ内では、通常「SELECT 1」や「SELECT *」と書きます（返す値自体は評価に影響しません）。
• サブクエリで関連付ける場合は、外側のテーブルの値を参照して紐付け条件を指定します。

具体例

1. 関連データが存在するレコードの抽出

たとえば「注文が存在する顧客」を抽出したい場合：

SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

このクエリは、orders テーブルに注文が存在する customer_id のみを customers テーブルから抽出します。

2. UPDATE や DELETE との組み合わせ

EXISTS は SELECT だけでなく、UPDATE や DELETE 文でも利用できます。

UPDATE users
SET status = 'active'
WHERE EXISTS (
  SELECT 1
  FROM purchases
  WHERE purchases.user_id = users.id
);

この例では、purchases テーブルに該当する購入履歴があるユーザーのみを「active」に更新します。

3. NOT EXISTS（否定）

NOT EXISTS を使うと「関連データが存在しない場合」を抽出できます。

SELECT customer_id
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

このクエリは、注文が一度もない顧客のみを抽出します。

EXISTS の特徴とメリット

• サブクエリが 1 件でも該当すれば即座に TRUE となり、無駄なデータ取得をしません。大規模データでも効率的に動作します。
• JOIN や IN 句と似た用途で使えますが、特に「存在チェック」に特化しています。
• サブクエリ内で外部テーブルの値を参照し、柔軟な条件指定が可能です。

「SELECT 1」は、SQL 文で「1 という値を返す」だけのシンプルな SELECT 文です。
例えば、

SELECT 1 FROM users;

と書くと、users テーブルの行数分だけ「1」が返されます（users テーブルに 3 行あれば「1」「1」「1」と 3 行分返る）。

特に EXISTS 句のサブクエリ内でよく使われます。EXISTS は「条件を満たすレコードが 1 件でも存在するか」を判定するため、サブクエリの SELECT で何を返すかは重要ではありません。
そのため、値として意味を持たせる必要がなく、単純に「1」や「*」を指定するのが一般的です。

例：

SELECT name
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
);

この場合、「orders テーブルに user_id が一致するデータが 1 件でもあれば、そのユーザーを抽出する」という意味になります。
要するに、「SELECT 1」は「値は何でもよいので、レコードの存在有無だけを判定したい」場面で使われる書き方です。

CASE 式

CASE 式は、SQL で条件分岐を実現するための構文です。
SELECT 句や ORDER BY 句、UPDATE 文などさまざまな場所で利用でき、条件に応じて異なる値や処理結果を返すことができます。
プログラミング言語の if 文や switch 文に近い役割を果たします。

CASE 式の種類

CASE 式には主に「単純 CASE 式」と「検索 CASE 式」の 2 種類があります。

1. 単純 CASE 式

特定の値に応じて結果を分岐させるシンプルな構文です。

SELECT
  user_id,
  CASE favorite_animal_id
    WHEN 1 THEN '犬'
    WHEN 2 THEN '猫'
    ELSE 'その他'
  END AS favorite_animal
FROM user;

favorite_animal_id の値によって「犬」「猫」「その他」と表示を切り替えます。

2. 検索 CASE 式

各 WHEN 句で条件式を指定できる、より柔軟な構文です。

SELECT
  user_name,
  CASE
    WHEN score >= 90 THEN 'S'
    WHEN score >= 70 THEN 'A'
    WHEN score >= 30 THEN 'B'
    WHEN score = 20 THEN 1 ELSE 0 END) AS "20歳以上"
FROM user
GROUP BY team;

年齢条件ごとに集計ができます。

ポイント

• CASE 式は SELECT 句だけでなく、ORDER BY や UPDATE、集計関数の中でも利用できます。
• WHERE 句や HAVING 句は「行の抽出」ですが、CASE 式は「値の変換や分類」に使います。
• 複雑な条件分岐やラベル付け、条件付きの集計など、SQL の表現力を大きく広げる構文です。

CASE 式は、SELECT 句でもWHERE 句でも使用できますが、使いどころや目的が異なります。

SELECT 句での CASE 式の役割

• 各行の値を条件ごとに変換したり、分類ラベルや計算結果を付与したい場合に使います。
• 例：点数によって「合格」「不合格」と表示を切り替える、都道府県ごとに「都民」「県民」と表示するなど。

SELECT name,
  CASE
    WHEN score >= 80 THEN '合格'
    ELSE '不合格'
  END AS result
FROM students;

WHERE 句での CASE 式の役割

• データの絞り込み条件を、レコードごとに動的に切り替えたい場合に使います。
• 例えば「点数が 80 点以上なら性別が男だけを抽出、それ以外は全て抽出」といった複雑な条件分岐が可能です。

SELECT *
FROM students
WHERE CASE
  WHEN score >= 80 THEN gender = '男'
  ELSE TRUE
END;

この例では、score が 80 点以上のときは gender が男の行だけ、それ以外は全ての行が抽出されます。

どちらで使うべきか？

• SELECT 句：値の変換やラベル付け、集計など「出力結果を加工したい」場合
• WHERE 句：データの抽出条件をレコードごとに柔軟に切り替えたい場合

注意点

• WHERE 句での CASE 式は複雑な条件分岐が必要な場合に有効ですが、通常はシンプルな条件式（AND/OR など）で十分なことが多いです。
• SELECT 句の CASE 式は、出力結果の加工や分類に最適です。
• パフォーマンスや可読性の観点からも、目的に応じて適切に使い分けることが重要です。

まとめ

CASE 式は SELECT 句・WHERE 句どちらでも使えますが、

• 「出力内容を変えたい」なら SELECT 句
• 「絞り込み条件を柔軟にしたい」なら WHERE 句
  で使うのが一般的です。

セット演算子

UNION

UNION は、複数の SELECT 文の結果を「縦に」統合し、1 つの結果セットとして返す SQL のセット演算子です。
UNION を使うことで、異なるテーブルや異なる条件で取得したデータをまとめて表示・活用できます。

基本構文

SELECT 列1, 列2 FROM テーブルA
UNION
SELECT 列1, 列2 FROM テーブルB;

• SELECT 文の列数・データ型・列の順序は全て一致している必要があります（列名は異なっても OK）
• 2 つ以上の SELECT 文をつなげて使うこともできます

UNION と UNION ALL の違い

• UNION は重複を排除するため、UNION ALL より処理コストが高くなります
• 重複がないことが分かっている場合や、パフォーマンス重視の場合は UNION ALL が推奨されます

使用例

1. 複数テーブルの同じ構造のデータをまとめる

SELECT name FROM users
UNION
SELECT name FROM admins;

users と admins の両方の name をまとめて一覧表示します（重複は排除）。

2. UNION ALL で重複も含めて取得

SELECT name FROM users
UNION ALL
SELECT name FROM admins;

重複する name も全て表示されます。

3. 3 つ以上の SELECT 文も OK

SELECT year, sales FROM sales_2021
UNION
SELECT year, sales FROM sales_2022
UNION
SELECT year, sales FROM sales_2023;

複数年の売上データをまとめて一覧表示できます。

各 SELECT 文で WHERE 句や集計関数も利用可能

SELECT name, age FROM users WHERE age >= 20
UNION
SELECT name, age FROM admins WHERE age >= 20;

各 SELECT 文ごとに条件や集計を設定できます。

INTERSECT と EXCEPT

INTERSECT（積集合）

2 つのクエリ結果の共通部分を取得する演算子です。両方のクエリに存在する行のみを返します。

基本構文

SELECT 列1, 列2 FROM テーブルA
INTERSECT
SELECT 列1, 列2 FROM テーブルB;

特徴とルール

• 列の構造一致が必須：両クエリの列数・データ型・順序が完全に一致する必要があります[1][2]
• 重複行の扱い：デフォルトで重複行を排除（1 行にまとめる）
• 重複を保持する場合：INTERSECT ALLを使用

  SELECT 列 FROM テーブルA
  INTERSECT ALL
  SELECT 列 FROM テーブルB;
  

使用例

-- 野菜テーブルと果物テーブル共通の商品を抽出
SELECT product_name FROM vegetables
INTERSECT
SELECT product_name FROM fruits;

EXCEPT（差集合）

左側クエリの結果から右側クエリの結果を引いた差分を取得する演算子です（左側にのみ存在する行を返す）。

基本構文

SELECT 列1, 列2 FROM テーブルA
EXCEPT
SELECT 列1, 列2 FROM テーブルB;

特徴とルール

• 列の構造一致が必須：INTERSECT と同様の列制約あり[3][4]
• データベースによる差異：
  • Oracle ではMINUSを使用

  SELECT 列 FROM テーブルA
  MINUS  -- Oracle専用構文
  SELECT 列 FROM テーブルB;
  

• 重複を保持する場合：EXCEPT ALLを使用

使用例

-- 注文履歴はあるが未入金の顧客を抽出
SELECT customer_id FROM orders
EXCEPT
SELECT customer_id FROM payments;

共通の注意点

1. 順序の重要性
   EXCEPT は左 → 右の順序で差分を計算するため、順序変更で結果が異なります[3][4]：

   -- 結果が異なる！
   SELECT * FROM A EXCEPT SELECT * FROM B;
   SELECT * FROM B EXCEPT SELECT * FROM A;
   

2. パフォーマンス最適化
   重複排除が不要な場合はALL版を使用（デフォルトより高速）。

3. データベース対応状況

   • MySQL：8.0.31 以降で対応
   • PostgreSQL/Oracle：標準サポート
   • SQL Server：EXCEPT/INTERSECTをサポート

実践例

共通顧客の抽出（INTERSECT）

SELECT customer_id FROM 2023_customers
INTERSECT
SELECT customer_id FROM 2024_customers;

→ 2023 年と 2024 年両方に存在する顧客 ID を取得

新規顧客の抽出（EXCEPT）

SELECT customer_id FROM 2024_customers
EXCEPT
SELECT customer_id FROM 2023_customers;

→ 2024 年に新規登場した顧客 ID を抽出

UNION の注意点

1. パフォーマンスの低下

UNION は、複数の SELECT 文の結果を統合する際に「重複行の排除」を自動的に行います。この重複チェックには追加の処理コストが発生し、特に大量データの場合は処理速度が大きく低下します。
重複排除が不要な場合は、UNION ALL を使う方が高速です。

2. 同じテーブルを複数回スキャンする非効率

同じテーブルに対して複数の SELECT 文を実行し、それを UNION でまとめると、そのテーブルを何度もスキャンすることになります。これにより、必要以上に I/O や CPU リソースを消費し、全体のクエリ実行時間が長くなります。
条件分岐や CASE 式などで 1 回のスキャンにまとめられる場合は、そちらの方が効率的です。

3. 構文上の制約やエラーのリスク

UNION を使う場合、SELECT 文ごとに「列数」「データ型」「列の順序」を揃える必要があります。これらが一致していないと SQL エラーになります。また、ORDER BY の位置や JOIN との違いなど、誤用によるエラーや意図しない結果も発生しやすくなります。

4. JOIN の代替ではない

UNION は「縦方向（行の追加）」の結合であり、JOIN のような「横方向（列の追加）」の結合とは役割が異なります。JOIN で実現できる処理を UNION で代用しようとすると、非効率で複雑な SQL になりがちです。

UNION の代用方法が有効になるパターンは、主に以下の 2 つのケース

1. 条件分岐や集計のために UNION を使っている場合

パターン例：

• 「ある条件のときは A 列、別の条件のときは B 列を出力したい」
• 「条件ごとに SELECT 文を分けて UNION で結合している」

代用方法：

• CASE 式や SELECT 句の条件分岐を活用する
  • 1 回のテーブルアクセスで済むため、SQL がシンプルかつ高速になります。
  • 実行計画もすっきりし、冗長なアクセスや重複処理を避けられます。

例：

-- 悪い例（UNIONで条件分岐）
SELECT id, 'A' AS type FROM items WHERE year = 2023
UNION
SELECT id, 'B' AS type FROM items WHERE year = 2024;

-- 良い例（CASE式で分岐）
SELECT id,
  CASE
    WHEN year = 2023 THEN 'A'
    WHEN year = 2024 THEN 'B'
    ELSE 'その他'
  END AS type
FROM items;

→ 1 回のスキャンで済み、パフォーマンスも向上します[1]。

2. OR 条件のパフォーマンス最適化

パターン例：

• WHERE 句で複数の OR 条件を使っているが、インデックスが効かず遅い場合

代用方法：

• UNION（または UNION ALL）で個別に SELECT し、まとめる
  • 各条件ごとにインデックスを活用できるため、パフォーマンスが向上する場合があります。

例：

-- 遅い例（OR条件）
SELECT * FROM employees WHERE department = 'Marketing' OR department = 'IT';

-- 速い例（UNIONで分割）
SELECT * FROM employees WHERE department = 'Marketing'
UNION
SELECT * FROM employees WHERE department = 'IT';

→ 各クエリが個別に最適化され、インデックスが効きやすくなります[2]。

まとめ

• 条件分岐や集計目的の UNION は、CASE 式や SELECT 句の条件分岐で代用可能
• OR 条件のパフォーマンス改善には、UNION（または UNION ALL）で分割する方法が有効

ウィンドウ関数とは

ウィンドウ関数（Window 関数）は、SQL で「各行に対して、指定した範囲（ウィンドウ）内のデータを使って集計や計算を行い、その結果を各行ごとに返す」強力な機能です。通常の集計関数（SUM, AVG, COUNT など）は GROUP BY を使うと行数が減りますが、ウィンドウ関数は元の行数を保ったまま、行ごとの相対的な情報（順位、累計、移動平均など）を計算できます。

ウィンドウ関数の基本構文

() OVER (
  PARTITION BY
  ORDER BY
  ROWS BETWEEN
)

• OVER 句：ウィンドウ関数を使う宣言
• PARTITION BY：グループ分け（省略可）
• ORDER BY：ウィンドウ内での並び順
• ROWS BETWEEN ...：計算範囲の細かい指定（省略可）

代表的なウィンドウ関数と用途

具体例

1. 累積合計

SELECT
  name,
  sales_date,
  sales_amount,
  SUM(sales_amount) OVER (ORDER BY sales_date) AS 累積売上
FROM sales;

→ 日付順に売上の累計を各行に表示

2. ランキング付与

SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS 順位
FROM students;

→ スコア順に順位を付与（同点でも順位はユニーク）

3. 移動平均（直近 3 日間）

SELECT
  商品名,
  売上日,
  売上金額,
  AVG(売上金額) OVER (
    PARTITION BY 商品名
    ORDER BY 売上日
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS 移動平均_3日
FROM 売上テーブル;

→ 商品ごとに直近 3 日間の平均売上を計算

4. 前月比の計算

SELECT
  月,
  売上金額,
  売上金額 - LAG(売上金額) OVER (ORDER BY 月) AS 前月差分
FROM 売上;

→ 各月の売上と前月との差分を表示

ウィンドウ関数のポイント

• 集計関数を「行ごと」に使いたいときに便利
• 分析・ランキング・時系列処理に強い
• GROUP BY と違い、元の行数を保ったまま計算できる
• OVER 句の使い方で柔軟な範囲指定やグループ化が可能

データ操作

INSERT（データ追加）

用途
テーブルに新しいレコード（行）を追加します。

基本構文

INSERT INTO テーブル名 (カラム1, カラム2, ...)
VALUES (値1, 値2, ...);

• カラム名を省略した場合は、すべてのカラムに値を指定する必要があります。
• 複数行の追加も可能です。

応用例

• サブクエリや SELECT 文の結果をそのまま INSERT することもできます。

INSERT INTO テーブル名 (カラム1, カラム2)
SELECT カラム1, カラム2 FROM 他テーブル WHERE 条件;

• カラム名と値の数は一致させる必要があります。

UPDATE（データ更新）

用途
既存のレコードの値を更新します。

基本構文

UPDATE テーブル名
SET カラム1 = 新しい値1, カラム2 = 新しい値2
WHERE 条件;

• WHERE 句を省略すると、全てのレコードが更新されますので注意が必要です。

応用例

• 複数カラムの同時更新や、IN 句・CASE 式・JOIN と組み合わせた柔軟な更新が可能です。

UPDATE employees
SET title = 'Ms.'
WHERE emp_no IN (10006, 10007, 10009);

• 条件ごとに異なる値を設定する場合は CASE 式を使います。

UPDATE employees
SET salary = CASE
  WHEN department = 'Sales' THEN salary * 1.1
  WHEN department = 'IT' THEN salary * 1.2
  ELSE salary
END
WHERE hire_date < '2022-01-01';

DELETE（データ削除）

用途
テーブルからレコード（行）を削除します。

基本構文

DELETE FROM テーブル名 WHERE 条件;

• WHERE 句を省略すると、テーブル内の全データが削除されます。
• 主キーや条件を指定してピンポイントで削除するのが一般的です。

応用例

• JOIN やサブクエリを使って複雑な条件で削除することも可能です。

DELETE FROM users WHERE user_id = 123;

DELETE FROM orders
USING customers
WHERE orders.customer_id = customers.customer_id AND customers.country = 'Japan';

MERGE（複合処理）

用途
2 つのテーブルを比較し、「一致すれば更新」「なければ追加」「条件によっては削除」といった複合処理を 1 つの SQL で実現します。
主にデータの同期やバッチ処理で使われます。

基本構文

MERGE INTO ターゲットテーブル AS t
USING ソーステーブル AS s
ON (t.主キー = s.主キー)
WHEN MATCHED THEN
  UPDATE SET t.カラム = s.カラム
WHEN NOT MATCHED THEN
  INSERT (カラム1, カラム2, ...) VALUES (s.カラム1, s.カラム2)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

• WHEN MATCHED：一致した場合の更新処理
• WHEN NOT MATCHED：一致しない場合の追加処理
• WHEN NOT MATCHED BY SOURCE：ターゲット側だけに存在する場合の削除処理（DB によっては未対応）

使用例

MERGE INTO user1 a
USING (SELECT id, name, age FROM user2) b
ON (a.id = b.id)
WHEN MATCHED THEN
  UPDATE SET a.name = b.name, a.age = b.age
WHEN NOT MATCHED BY TARGET THEN
  INSERT (id, name, age) VALUES (b.id, b.name, b.age)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

• これにより、追加・更新・削除を一括で実行できます。

まとめ

• INSERT：新規データの追加
• UPDATE：既存データの更新
• DELETE：既存データの削除
• MERGE：追加・更新・削除を条件に応じて一括実行

USING の使い方

基本的な役割

USING 句は、SQL の MERGE 文や JOIN 文で「結合や比較のためのソースデータ（参照元テーブルやサブクエリ）」を指定するための構文です。特に MERGE 文では、更新・追加・削除などの条件判定に使う「比較対象のデータセット」を明示します。

MERGE 文における USING 句

MERGE 文は、2 つのテーブル（またはサブクエリ）を比較し、「一致すれば更新」「なければ追加」「場合によっては削除」といった複合処理を 1 つの SQL で実現します。
このとき、USING 句で「比較元となるテーブルやサブクエリ」を指定します。

構文例

MERGE INTO 対象テーブル AS t
USING 参照テーブル（またはサブクエリ） AS s
ON (t.キー列 = s.キー列)
WHEN MATCHED THEN
  UPDATE SET ...
WHEN NOT MATCHED THEN
  INSERT (...);

サンプル

MERGE INTO user1 a
USING (SELECT id, name, age FROM user2) b
ON (a.id = b.id)
WHEN MATCHED THEN
  UPDATE SET a.name = b.name, a.age = b.age
WHEN NOT MATCHED THEN
  INSERT (id, name, age) VALUES (b.id, b.name, b.age);

• MERGE INTO user1 a：更新先テーブル（ターゲット）
• USING (SELECT id, name, age FROM user2) b：比較元（ソース）
• ON (a.id = b.id)：結合条件

このように、USING 句で指定したデータセットとターゲットテーブルを ON 句で結合し、条件ごとに UPDATE や INSERT を実行します。

JOIN 文における USING 句

JOIN 文でも USING 句は使えます。
この場合は「両テーブルで同じ名前のカラムを指定し、そのカラムで結合する」ことを意味します。

SELECT *
FROM table1
JOIN table2
USING (共通カラム);

ポイント

• MERGE 文の USING 句は「比較元データセット（テーブルやサブクエリ）」を指定する
• ON 句で「どのカラムで結合・比較するか」を明示する
• 複数カラムや副問合せも指定可能
• JOIN 文の USING 句は「同じ名前のカラムで自動的に結合」する

まとめ

USING 句は、MERGE 文では「比較・結合対象のデータセット」を指定し、ON 句で比較条件を定義します。
JOIN 文でも「共通カラムによる結合」に使われます。
これにより、複雑なデータ統合や同期処理をシンプルに記述できます。

パフォーマンスとベストプラクティス

GROUP BY での注意点

GROUP BY 句で集約操作（集計関数）を行った場合、SELECT 句で使用できるものは以下の 3 つに限定されます。

1. GROUP BY で指定したカラム（集約キー）

• グループ化に使ったカラム（例：都道府県名やカテゴリなど）
• 複数カラムでグループ化した場合は、そのすべてのカラム

2. 集計関数

• SUM, COUNT, AVG, MAX, MIN などの集計関数を使った式
• 例：SUM(population), COUNT(*), AVG(score)など

3. 定数

• 例えば SELECT '合計' AS label, SUM(price) FROM ... GROUP BY ... のように、定数値も SELECT 句で指定可能

注意点

• GROUP BY で指定していないカラムを SELECT 句に書くとエラーになります（標準 SQL の場合）。
• SELECT 句で付けた別名（AS でつけたエイリアス）は、同じ SELECT の中では参照できません（評価順序のため）。
• WHERE 句では集計関数は使えません。集計後の条件指定は HAVING 句を使います。

まとめ

GROUP BY で集約操作を行った場合、SELECT 句で使えるのは：

• GROUP BY 句で指定したカラム（集約キー）
• 集計関数
• 定数

CASE 式と GROUP BY の活用

エラーが発生するパターン

1. GROUP BY 未指定列の SELECT
   CASE 式で生成した列を SELECT 句で使用する場合、その列をGROUP BY に含めないとエラーになります：

   -- エラー例: group_nameはGROUP BYに未指定
   SELECT
     CASE
       WHEN age = 80 THEN '合格'
       ELSE 0 -- 数値と文字列の型不一致
     END AS result
   FROM exams
   GROUP BY result;
   

応用テクニック

1. 条件別集計（クロス集計）

SELECT
  department,
  COUNT(*) AS total,
  COUNT(CASE WHEN gender = 'M' THEN 1 ELSE NULL END) AS male_count,
  AVG(CASE WHEN position = 'Manager' THEN salary ELSE NULL END) AS manager_avg_salary
FROM employees
GROUP BY department;

• ポイント:
  CASE 式を集計関数内で使用し、ELSE NULLで不要な行を除外します。

2. 動的なグループ化

SELECT
  CASE
    WHEN age BETWEEN 18 AND 24 THEN '18-24歳'
    WHEN age BETWEEN 25 AND 34 THEN '25-34歳'
    ELSE '35歳以上'
  END AS age_group,
  COUNT(*)
FROM users
GROUP BY
  CASE
    WHEN age BETWEEN 18 AND 24 THEN '18-24歳'
    WHEN age BETWEEN 25 AND 34 THEN '25-34歳'
    ELSE '35歳以上'
  END;

• ポイント:
  SELECT 句の CASE 式を GROUP BY で同じ式で繰り返す必要があります。

3. 行 → 列変換（ピボット）

SELECT
  product_id,
  MAX(CASE WHEN year = 2023 THEN sales END) AS sales_2023,
  MAX(CASE WHEN year = 2024 THEN sales END) AS sales_2024
FROM sales
GROUP BY product_id;

• ポイント:
  MAX/MINで NULL を無視し、横持ちデータを生成。

エラー回避のベストプラクティス

1. GROUP BY の対象を明確に
   SELECT 句の非集約列はすべて GROUP BY に含めるか、集計関数で囲みます。

2. 集計関数内で CASE 式を使用

   SELECT
     department,
     SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count
   FROM employees
   GROUP BY department;
   

3. データ型の統一
   CASE 式の全分岐で同じデータ型を返すようにします。

4. 別名の再利用
   PostgreSQL/MySQL では SELECT 句の別名を GROUP BY で再利用可能：

   SELECT
     CASE ... END AS category
   FROM table
   GROUP BY category; -- 別名でグループ化
   

パフォーマンス向上のポイント

• 1 回のテーブルスキャン
  UNION を使った複数クエリの代わりに CASE 式を活用することで、テーブルアクセスを 1 回に抑えられます。
• インデックス活用
  WHERE 句ではなく SELECT 句でフィルタリングすることで、インデックスを効果的に使用可能。

重要な原則:
「WHERE 句で分岐させるのは素人、プロは SELECT 句で分岐させる」
このアプローチでクエリをシンプルにし、パフォーマンスを最大化できます。

サブクエリの問題点と対策

サブクエリが引き起こす主な弊害は、以下の通りです。

1. パフォーマンス低下のリスク

• サブクエリはメインクエリとは別に実行されるため、実行時間が増加しやすいです。特に大規模データベースでは、サブクエリの処理に多くの時間がかかることがあります。
• サブクエリの結果を一時的なワークテーブルや TEMP 領域に格納する場合、メモリ不足による TEMP 落ちが発生し、ディスクアクセスが増えてさらに処理速度が低下します。
• サブクエリを含む SQL は、テーブルを複数回スキャンすることが多く、I/O コストが増大します。

2. SQL の可読性・保守性の低下

• サブクエリを多用すると、SQL 文が複雑になりやすく、可読性やメンテナンス性が悪化します。
• ネストが深くなると、ロジックの把握やバグ発見が困難になり、チーム開発やレビュー時の障害にもなります。

3. 実行計画の複雑化と最適化困難

• サブクエリは実行計画を複雑にし、オプティマイザによる最適化が難しくなる場合があります。
• 特に相関サブクエリや複数回同じサブクエリを使う場合、毎回再計算が発生しやすく、パフォーマンスが著しく落ちることがあります。

4. IN 句サブクエリのパフォーマンス問題

• IN句でサブクエリを使うと、サブクエリの結果が一時テーブルに格納され、インデックスが効かず全件走査になることが多いです。
• 一方、EXISTSを使うとインデックスが効きやすく、パフォーマンスが改善される場合があります。

5. TEMP 落ち・リソース枯渇

• サブクエリの結果が大きい場合や複雑な集計・結合を含む場合、メモリ不足により TEMP 領域への退避（TEMP 落ち）が発生し、ディスク I/O 増加やエラーの原因となります。

サブクエリのパフォーマンス改善策

1. 相関サブクエリを JOIN に置き換える

相関サブクエリ（外部クエリの各行で実行されるサブクエリ）は特に遅くなります。JOIN で書き換えることで大幅に改善できます。

改善例:

-- 非効率な相関サブクエリ
SELECT customer_id
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

-- 効率的なJOIN使用
SELECT DISTINCT c.customer_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

2. IN 句を JOIN/EXISTS で置き換える

IN 句のサブクエリは中間テーブルを作成しがちです。JOIN や EXISTS で代替しましょう。

改善例:

-- 非効率なIN句
SELECT *
FROM products
WHERE category_id IN (
  SELECT category_id
  FROM categories
  WHERE type = 'Electronics'
);

-- 効率的なJOIN使用
SELECT p.*
FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE c.type = 'Electronics';

3. EXISTS の最適化

EXISTS は IN より効率的ですが、以下の点に注意：

• サブクエリ内でインデックスが効く条件を使用
• サブクエリを最小限に絞り込む

最適化例:

SELECT *
FROM employees e
WHERE EXISTS (
  SELECT 1
  FROM sales s
  WHERE s.employee_id = e.id
  AND s.year = 2023 -- 絞り込み条件を追加
);

4. サブクエリの結果を一時テーブル化

複雑なサブクエリは WITH 句（CTE）で事前に計算：

WITH monthly_sales AS (
  SELECT
    product_id,
    SUM(amount) AS total_sales
  FROM sales
  GROUP BY product_id
)
SELECT p.name, ms.total_sales
FROM products p
JOIN monthly_sales ms ON p.id = ms.product_id;

5. インデックスの適切な設計

サブクエリ内で使用される列にインデックスを設定：

• WHERE 句の条件列
• JOIN の結合キー
• 集計関数の対象列

6. DISTINCT/ORDER BY の見直し

不必要な操作は削除：

-- 非効率
SELECT DISTINCT department
FROM employees
WHERE salary > 50000;

-- 効率的（GROUP BYで代用）
SELECT department
FROM employees
WHERE salary > 50000
GROUP BY department;

7. 実行計画の分析

EXPLAINコマンドで問題箇所を特定：

EXPLAIN
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'Japan'
);

出力結果から：

• フルテーブルスキャンが発生していないか
• 適切なインデックスが使用されているか
• サブクエリのコストが高いか

効果的な改善のポイント

サブクエリのパフォーマンス改善策とウィンドウ関数活用

1. 相関サブクエリをウィンドウ関数に置換

相関サブクエリは各行ごとに実行されるため非効率です。ウィンドウ関数で 1 回のスキャンで処理します。

改善例:

-- 非効率な相関サブクエリ
SELECT employee_id, salary,
  (SELECT AVG(salary)
   FROM employees e2
   WHERE e2.department = e1.department) AS avg_dept_salary
FROM employees e1;

-- 効率的なウィンドウ関数
SELECT employee_id, salary,
  AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM employees;

2. 集計サブクエリをウィンドウ関数に置換

GROUP BY サブクエリの代わりに、集計関数と OVER 句を組み合わせます。

改善例:

-- 非効率なサブクエリ
SELECT e.department,
  (SELECT COUNT(*)
   FROM employees
   WHERE department = e.department) AS emp_count
FROM departments e;

-- 効率的なウィンドウ関数
SELECT DISTINCT department,
  COUNT(*) OVER (PARTITION BY department) AS emp_count
FROM employees;

3. ランキング処理の最適化

ROW_NUMBER/RANK 関数で複数回のテーブルアクセスを削減します。

改善例:

-- 非効率な方法
SELECT product_id, price
FROM products p1
WHERE price = (
  SELECT MAX(price)
  FROM products p2
  WHERE p2.category = p1.category
);

-- 効率的なウィンドウ関数
WITH ranked_products AS (
  SELECT product_id, price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rnk
  FROM products
)
SELECT product_id, price
FROM ranked_products
WHERE rnk = 1;

4. 時系列分析の効率化

LAG/LEAD 関数で前後の値にアクセスし、自己結合を回避します。

改善例:

-- 非効率な自己結合
SELECT t1.date, t1.sales, t2.sales AS prev_sales
FROM sales t1
LEFT JOIN sales t2 ON t1.date = t2.date + 1;

-- 効率的なウィンドウ関数
SELECT date, sales,
  LAG(sales) OVER (ORDER BY date) AS prev_sales
FROM sales;

5. パーティション分割集計

PARTITION BY でグループ化し、複数サブクエリを一元化します。

改善例:

-- 非効率な複数サブクエリ
SELECT product_id,
  (SELECT SUM(sales) FROM sales WHERE product_id = p.id AND year=2023) AS sales_2023,
  (SELECT SUM(sales) FROM sales WHERE product_id = p.id AND year=2024) AS sales_2024
FROM products p;

-- 効率的なウィンドウ関数
SELECT product_id,
  SUM(CASE WHEN year=2023 THEN sales END) OVER (PARTITION BY product_id) AS sales_2023,
  SUM(CASE WHEN year=2024 THEN sales END) OVER (PARTITION BY product_id) AS sales_2024
FROM sales;

ウィンドウ関数のメリット

1. パフォーマンス向上

   • テーブルスキャン回数を削減（1 回のスキャンで複数集計可能）
   • 中間テーブルの生成を回避
   • インデックスを効果的に活用

2. 可読性向上

   • 複雑なネスト構造を解消
   • ロジックが直感的に理解可能

3. メンテナンス性向上

   • クエリ修正が容易
   • コードの重複を削減

パフォーマンス比較

大規模データ（100 万行）での実測値：ウィンドウ関数はサブクエリ比で最大 9 倍の性能向上

適用ポイント

• 同じテーブルへの複数アクセスがある場合
• グループごとのランキング/順位付け
• 時系列データの前後比較
• 複数条件の集計を同時に行う場合

ウィンドウ関数は SQL 標準機能として主要 DBMS（MySQL 8.0+, PostgreSQL, SQL Server, Oracle）でサポートされています。実行計画を確認し、適切なパーティショニングとインデックス設計を行うことで、さらに効果を発揮します。

サブクエリがパフォーマンスが良いパターンと SQL 例

1. データの事前絞り込みや分割が有効な場合

サブクエリで対象データを絞り込み、メインクエリの処理対象を減らすことで効率化。

SELECT *
FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'Japan'
);

2. EXISTS サブクエリによる存在チェック

EXISTS は条件に合致する行が 1 件見つかれば検索を打ち切るため高速。

SELECT *
FROM employees e
WHERE EXISTS (
  SELECT 1 FROM sales s WHERE s.employee_id = e.id
);

3. 集計やランキング、サマリー処理

サブクエリで集計結果を一時的に扱い、メインクエリで効率的に処理。

SELECT department, emp_count
FROM (
  SELECT department, COUNT(*) AS emp_count
  FROM employees
  GROUP BY department
) AS dept_counts
WHERE emp_count > 10;

4. 複雑な条件や順序制御

サブクエリで処理順序を明示的に制御し、余計なデータを減らす。

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) AS ranked
WHERE rn = 1;

5. UPDATE/INSERT/DELETE の値取得

他テーブルの値をサブクエリで取得し効率的に反映。

UPDATE employees
SET salary = (
  SELECT AVG(salary) FROM employees WHERE department = 'Sales'
)
WHERE department = 'Sales';

6. 複雑な論理を分割してシンプルに

大きな問題を小さなサブクエリに分割し、可読性とパフォーマンス向上。

SELECT *
FROM (
  SELECT * FROM orders WHERE order_date >= '2025-01-01'
) AS recent_orders
WHERE total_amount > 1000;

相関サブクエリとは

相関サブクエリとは、サブクエリ（副問い合わせ）内で外側（親）のクエリのカラムを参照するタイプのサブクエリです。
このため、親クエリの各行ごとにサブクエリが個別に実行され、その結果をもとに最終的な結果が決まります。

特徴

• 「相関」している
  サブクエリが、外側のクエリの値（カラム）を使って処理されるため、親クエリの行ごとにサブクエリの内容が変化します。
• 各行ごとにサブクエリが実行される
  非相関サブクエリは一度だけ実行されますが、相関サブクエリは親クエリの行数分だけ繰り返しサブクエリが評価されます。
• グループ内比較や条件付きの集計など、行ごとの柔軟な比較処理に便利
  ただし、パフォーマンス面では JOIN やウィンドウ関数のほうが有利な場合も多いです。

例：type ごとに power が最大の動物を抽出

SELECT *
FROM animals AS outer_animal
WHERE outer_animal.power = (
  SELECT MAX(inner_animal.power)
  FROM animals AS inner_animal
  WHERE inner_animal.type = outer_animal.type
);

この例では、親クエリ（outer_animal）の各行の type ごとに、サブクエリで同じ type の最大 power を求め、その値と一致する行だけを抽出します[2]。

例：顧客ごとの最高注文金額

SELECT
  c.customer_id,
  c.customer_name,
  (
    SELECT MAX(order_amount)
    FROM orders o
    WHERE o.customer_id = c.customer_id
  ) AS max_order_amount
FROM customers c;

この例では、各顧客ごとに、その顧客の注文の中で最大の金額をサブクエリで取得しています[6]。

相関サブクエリの動作イメージ

1. 親クエリの 1 行目を処理
2. その行の値を使ってサブクエリを実行
3. サブクエリの結果で条件判定や値の取得
4. 次の行に進み、1 ～ 3 を繰り返す

注意点

• パフォーマンスが低下しやすい
  行数が多い場合、サブクエリが何度も実行されるため、処理が重くなりやすいです。
• JOIN やウィンドウ関数で代替できる場合は、そちらの方が高速なことが多い
  ただし、相関サブクエリでしか書けないロジックや、SQL をシンプルに保てる場面もあります。

まとめ
相関サブクエリは「サブクエリ内で親クエリのカラムを参照し、親クエリの各行ごとにサブクエリが実行される」構造のサブクエリです。柔軟な条件付き集計や比較に便利ですが、パフォーマンスに注意が必要です。

この SQL の処理内容について ①

この SQL は「複合主キー（ここでは class と student_id の 2 つのカラム）を持つテーブルに対して、各行ごとにその行の順序（シーケンス番号）を計算する」ためのものです。

SELECT class, student_id,
  (SELECT COUNT(*)
     FROM Weights2 W2
    WHERE (W2.class, W2.student_id)
         <= (W1.class, W1.student_id)
  ) AS seq
FROM Weights2 W1;

具体的な処理の流れ

• 外側のクエリ（FROM Weights2 W1）で、テーブル Weights2 の全行を 1 行ずつ処理します。
• 各行ごとに、内側のサブクエリ（SELECT COUNT(*) ...）が実行されます。
• サブクエリでは、「W2.class, W2.student_id が W1.class, W1.student_id 以下である」行の数をカウントしています。
  • つまり、(class, student_id) のタプルが「現在の行以下」である行数を数えています。
  • これにより、その行が全体の中で何番目に位置するか（1 始まりの連番）が seq として得られます。

この SQL の特徴

• 相関サブクエリを使っています。サブクエリ内で外側のクエリの値（W1.class, W1.student_id）を参照しているため、各行ごとにサブクエリが再評価されます。
• 複合主キー（複数列の組み合わせで一意性を持つ主キー）に対応しています。「(class, student_id)」のようにタプルで比較することで、複数列の組み合わせ順序を考慮した連番付与ができます。
• データ型の組み合わせが自由で、数値型・文字列型・日付型など、どんな型でも利用できるというメリットがあります（下部の説明文より）。

どんなときに使うか

• テーブルの複合主キーごとに「順序」や「連番」を付けたいとき。
• SQL だけで、ORDER BY や ROW_NUMBER()ウィンドウ関数が使えない環境で連番を付与したいとき。

注意点

• 各行ごとにサブクエリが実行されるため、データ量が多い場合はパフォーマンスに注意が必要です。
• ウィンドウ関数（ROW_NUMBER()など）が使える場合は、そちらの方が高速です。

まとめ

この SQL は「複合主キーを持つテーブルの各行に、タプル順での連番（シーケンス番号）を付ける」処理を行っています。サブクエリで外側の行を参照する「相関サブクエリ」の技法を使っており、主キーが複数列でも柔軟に対応できるのが特徴です。

この SQL の処理内容について ②

この SQL は、テーブル「Numbers」に格納されている「num」列の値について、各行の次に来る値（次の行の値）を取得するためのものです。
具体的には、「num」と「next_num」を並べて表示し、「next_num」は現在行の次の行の「num」値を示しています。

SQL 文の内容

SELECT num,
       MAX(num)
           OVER(ORDER BY num
                ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_num
FROM Numbers;

各要素の意味

• num
  現在の行の番号（座席番号など）。

• MAX(num) OVER(...) AS next_num
  ウィンドウ関数を使い、現在の行から「1 行後」だけを範囲にして、その範囲内の最大値（この場合は次の行の num 値）を取得し、「next_num」として表示。

• ORDER BY num
  「num」順に並べ替えた上で処理。

• ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
  現在行から「1 行後」から「1 行後」まで、つまり「次の 1 行だけ」をウィンドウの範囲とする指定。

実行結果例

何がわかるか

• 「num」列は現在の番号。
• 「next_num」列は、次の行の番号（次に空いている座席番号など）。
• この 2 つの差が 1 でなければ、間に欠番（抜け）があることがわかります。
• 例えば、「1」と「3」の間には「2」が抜けていることがわかります。

解説

この SQL は、自己結合やサブクエリを使わずに、ウィンドウ関数だけで「次の行の値」を簡単に取得するテクニックです。
「ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING」を使うことで、直後の 1 行だけを対象にできるのがポイントです。

FOLLOWING

FOLLOWINGは、SQL のウィンドウ関数（分析関数）で「現在の行より後ろ」を基準に範囲を指定するためのキーワードです。

主な使い方

• n FOLLOWING
  現在の行から「n 行後」までを指します。たとえば、1 FOLLOWINGなら「現在の行の 1 つ後ろの行」を意味します。

• UNBOUNDED FOLLOWING
  現在の行からパーティション（または全体）の最後の行まで、すべてを範囲に含める指定です。

例

SUM(sales) OVER (
  ORDER BY sales_date
  ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
)

この場合、現在の行と、その後ろ 2 行までを集計対象とします。

よく使うパターン

• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  現在の行を中心に、前後 1 行ずつ含めた 3 行がウィンドウの範囲になります。

• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  現在の行から最後の行までが範囲となります。

ポイント

• FOLLOWINGは、ORDER BYで並び替えた順序に基づいて「後ろ」の行を指定します。
• nには 0 以上の整数を指定します（負の数は不可）。
• ウィンドウ関数のフレーム指定で、集計や分析の範囲を柔軟にコントロールできます。

まとめ
FOLLOWINGは「現在の行より後ろの n 行」や「最後の行まで」など、ウィンドウ関数で集計する範囲の終点や始点を指定するために使うキーワードです。

この SQL の処理内容について ③

この SQL は、連番（num）において「抜けている番号（ギャップ）」の範囲を検出するクエリです。たとえば、1,2,3,6,7,8 というデータがあった場合、「4,5」が抜けている（ギャップ）ことを見つけるためのものです。

クエリの構造

SELECT num + 1 AS gap_start,
       (num + diff - 1) AS gap_end
FROM (
  SELECT num,
         MAX(num) OVER(ORDER BY num ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - num AS diff
  FROM Numbers
) TMP(num, diff)
WHERE diff <> 1;

各部分の役割

• サブクエリ

  • SELECT num, MAX(num) OVER(ORDER BY num ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - num AS diff FROM Numbers
    • 各行について「次の行の num」と「現在の num」の差（diff）を計算します。
    • MAX(num) OVER(ORDER BY num ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)は「次の行の num」を取得しています。
    • 例えば、現在の num が 3、次の num が 6 なら、diff は 3 になります。

• メインクエリ

  • SELECT num + 1 AS gap_start, (num + diff - 1) AS gap_end ... WHERE diff <> 1
    • diff が 1 でない（つまり連番が途切れている）場合のみを抽出します。
    • 抜けている番号の開始（gap_start）は「現在の num + 1」。
    • 抜けている番号の終了（gap_end）は「現在の num + diff - 1」です。
    • 例：num=3, diff=3 の場合、gap_start=4, gap_end=5 となり、「4,5」が抜けていることが分かります。

まとめ

• この SQL は、連番の抜け（ギャップ）を範囲として抽出するクエリです。
• ウィンドウ関数（MAX ... OVER）を使い、次の行との値の差を計算し、その差が 1 でない場合にギャップ範囲を算出しています。
• これにより、手続き型のループ処理を SQL で効率よく置き換えています。

参考例

もしデータが 1,2,3,6,7,8 なら、このクエリは「gap_start=4, gap_end=5」と出力し、「4,5」が抜けていることを示します。

このようなギャップ検出は、連番や日付の欠損チェックなど、データ品質管理や連続性チェックに広く使われます。

TMP(num, diff)は、サブクエリの結果に対して「TMP」という一時的なテーブル名（エイリアス）を付け、そのテーブルのカラム名を「num」と「diff」と明示的に指定している部分です。

SQL では、サブクエリや WITH 句の結果に名前（エイリアス）を付けることで、外側の SELECT 文からその結果を参照しやすくします。
この場合、サブクエリの結果に「TMP」という名前を付け、さらにそのカラム名を「num」「diff」と定義しています。

具体的な例

FROM (
  SELECT num,
         MAX(num) OVER(ORDER BY num ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - num AS diff
  FROM Numbers
) TMP(num, diff)

• TMPはサブクエリのエイリアス（別名）です。
• (num, diff)は、そのサブクエリの結果セットのカラム名を明示的に指定しています。

このようにすることで、外側の SELECT 文でTMP.numやTMP.diffとして値を参照できます。

ポイント

• 一時テーブルや物理的なテーブルではなく、「サブクエリの結果セット」に名前を付けているだけです。
• 一時的に作られる仮想的なテーブルとして扱われます。

この構文は、SQL の標準的なサブクエリのエイリアス指定方法であり、複雑なクエリを分かりやすく記述するために使われます。

SQL の COALESCE 関数の使い方

COALESCE 関数は、SQL で NULL 値を扱う際に非常に便利な関数です。指定した複数の引数の中から、左から順に評価して最初に NULL でない値を返します。全ての引数が NULL の場合は NULL を返します。

基本構文

COALESCE(値1, 値2, ..., 値N)

• 引数は 2 つ以上指定可能です。
• 左から順に評価し、最初に NULL でない値を返します。

使い方の例

1. NULL 値の置き換え

たとえば、quantityカラムが NULL の場合に 0 を表示したい場合:

SELECT id, name, price, COALESCE(quantity, 0) AS quantity
FROM purchase;

この例では、quantityが NULL なら 0 が表示されます。

2. 複数カラムの統合

複数のカラムのうち、どれか一つでも値があればその値を表示したい場合:

SELECT id, COALESCE(name1, name2, '名無し') AS name
FROM customer;

• name1が NULL でなければその値を表示
• name1が NULL でname2に値があればname2を表示
• 両方 NULL なら「名無し」と表示

3. フォールバック値の設定

社員の自宅電話番号がなければ勤務先電話番号を表示したい場合:

SELECT first_name, COALESCE(phone_home, phone_work) AS phone_number
FROM employees;

• phone_homeが NULL ならphone_workが返されます。

よくある利用シーン

• デフォルト値の設定（NULL 時に 0 や空文字などを返す）
• 複数カラムの値のマージ
• OUTER JOIN 時の NULL 補完
• 集計時の NULL 値の扱い（例：平均計算時に NULL を 0 として扱う）

IFNULL や NVL との違い

• COALESCE(a, b)は、aが NULL ならbを返す（引数 2 つの場合はIFNULLやNVLと同じ動作）
• 3 つ以上の引数が指定できる点が特徴

注意点

• 全ての引数が NULL の場合は NULL を返します。
• 引数のデータ型は互換性が必要です。

読み方

「コアレス」「コウアレス」「コーレス」などと呼ばれます。

SQL の SIGN 関数について（例 SQL 付き）

SIGN 関数は、指定した数値の符号（正・負・ゼロ）を判定し、
その結果に応じて「1」「0」「-1」のいずれかを返す関数です。

基本構文

SIGN(数値式)

• 数値式には、数値または数値を返す式を指定します。

戻り値のルール

例 1：単純な数値での使用

SELECT SIGN(100)   AS sign_pos,  -- 結果: 1
       SIGN(0)     AS sign_zero, -- 結果: 0
       SIGN(-25)   AS sign_neg;  -- 結果: -1

例 2：テーブルのカラムに対して使用

売上テーブルsalesのamountカラムについて、
金額がプラス・マイナス・ゼロかを判定する例です。

SELECT amount,
       SIGN(amount) AS amount_sign
FROM sales;

この例では、各レコードのamountが

• プラスなら「1」
• ゼロなら「0」
• マイナスなら「-1」
  としてamount_signに表示されます。

例 3：CASE 式と組み合わせて判定

SIGN 関数と CASE 式を組み合わせて、
金額の増減を文字列で表現する例です。

SELECT amount,
       CASE SIGN(amount)
           WHEN 1  THEN '増加'
           WHEN 0  THEN '変化なし'
           WHEN -1 THEN '減少'
           ELSE '不明'
       END AS 状態
FROM sales;

主な用途

• データの増減や傾向の判定
• 条件分岐やフラグ判定
• 統計分析や集計処理

注意点

• 引数が NULL の場合は NULL を返します。
• 多くの主要な RDBMS（MySQL、PostgreSQL、SQL Server、Oracle など）で利用できます。

INSERT SELECT の使い方とメリット

INSERT SELECT とは

INSERT SELECTは、あるテーブルのデータを SELECT 文で取得し、そのまま別のテーブルに一括で挿入する SQL 構文です。手動で VALUES を羅列する必要がなく、既存データを効率良くコピー・移動・加工できます。

基本構文

INSERT INTO テーブル名 (カラム1, カラム2, ...)
SELECT カラム1, カラム2, ...
FROM 元テーブル
WHERE 条件;

• INSERT INTOの後に挿入先テーブルとカラムを指定
• SELECT文で挿入したいデータを取得
• 必要に応じてWHEREで条件を絞る

具体例

例 1：全件コピー

INSERT INTO users_backup (id, name, email)
SELECT id, name, email FROM users;

usersテーブルの全データをusers_backupテーブルにコピーします。

例 2：条件付きコピー

INSERT INTO users_backup (id, name, email)
SELECT id, name, email FROM users
WHERE created_at >= '2025-01-01';

2025 年以降に作成されたユーザーのみバックアップテーブルに挿入します。

例 3：一部カラムのみコピー

INSERT INTO short_users (name)
SELECT name FROM users WHERE LENGTH(name) <= 5;

名前が 5 文字以下のユーザーだけ、short_usersテーブルに名前だけ挿入します。

主なメリット

• 大量データの一括挿入が高速
  手作業やループ処理よりも、SQL サーバー内部で一括処理されるため圧倒的に速いです。
• データ移行やバックアップが簡単
  テーブル構造が同じなら、簡単にデータを複製・移動できます。
• 条件付きデータ抽出・加工が容易
  SELECTに条件や関数を組み合わせて、必要なデータだけを柔軟に挿入できます。
• 人為的なミスを減らせる
  手入力による VALUES 指定よりも安全で、ミスや漏れが起きにくいです。

注意点

• 挿入先と取得元のカラム数・データ型が一致している必要があります。
• 挿入先テーブルの既存データはそのまま残り、新規行として追加されます。
• 大量データを扱う場合はトランザクションやロックに注意しましょう。

この SQL 文は、「注文単位の集約」を行い、各注文ごとに最大の遅延日数（納品日と注文日の差）を求め、その注文者名も一緒に取得するものです[1]。

Group 化する際の MAX(O.order_name)のようなテクニック

SELECT O.order_id,
       MAX(O.order_name),
       MAX(ORC.delivery_date - O.order_date) AS max_diff_days
FROM Orders O
INNER JOIN OrderReceipts ORC
    ON O.order_id = ORC.order_id
WHERE ORC.delivery_date - O.order_date >= 3
GROUP BY O.order_id;

各項目の意味

• O.order_id
  注文 ID ごとに集約（グループ化）します。

• MAX(O.order_name)
  注文 ID ごとに注文者名の最大値を取得します。
  通常、注文 ID と注文者名が 1 対 1 で対応している場合、MAX を使ってもその注文者名が返ります。
  ここでは「グループ化した結果に注文者名を含める」ためのテクニックです。
  group by で指定していないとエラーになるため、Max 関数や Min 関数で指定すると取得できる

• MAX(ORC.delivery_date - O.order_date) AS max_diff_days
  注文ごとに納品日と注文日の差（遅延日数）の最大値を計算し、max_diff_days として表示します。

• WHERE ORC.delivery_date - O.order_date >= 3
  納品日と注文日の差が 3 日以上のデータだけを対象にします。

• GROUP BY O.order_id
  注文 ID ごとに集約します。

実行結果の意味

• order_id：注文番号
• max：注文者名（MAX 関数で取得）
• max_diff_days：その注文で発生した最大の遅延日数

補足

注文 ID と注文者名が 1 対 1 で対応している前提なので、MAX(order_name)で正しい注文者名が取得できます。もし 1 対多の場合は注意が必要です。

SQL でインデックスが効かなくなる条件とその対策を、検索結果から網羅的に整理します。インデックスはデータベースの検索性能を向上させる重要な仕組みですが、特定のクエリの書き方や条件によっては効果が発揮されません。以下に主要なケースと解決策をまとめます。

1. 列に対する演算や関数の適用

インデックス列に計算や関数を適用すると、インデックスが無効化されます。
例:

SELECT * FROM products WHERE price * 1.1 > 100;  -- 演算
SELECT * FROM users WHERE SUBSTR(name, 1, 1) = 'A';  -- 関数

対策:

• 演算を右辺（比較値側）に移動する。

  SELECT * FROM products WHERE price > 100 / 1.1;
  

• 関数の使用を避け、代わりに範囲検索などで代替する。

  SELECT * FROM users WHERE name LIKE 'A%';  -- 前方一致で代替
  

2. データ型の不一致

インデックス列と比較値のデータ型が異なる場合、暗黙的な型変換が発生しインデックスが無効になります。
例:

SELECT * FROM users WHERE id = '100';  -- idが数値型なのに文字列で比較

対策:

• 比較値の型をインデックス列と一致させる。

  SELECT * FROM users WHERE id = 100;
  

3. LIKE 句の中間一致・後方一致

B-tree インデックスは前方一致のみ有効で、中間一致や後方一致では機能しません。
例:

SELECT * FROM users WHERE name LIKE '%田中%';  -- 中間一致
SELECT * FROM users WHERE name LIKE '%田中';    -- 後方一致

対策:

• 前方一致に書き換える。

  SELECT * FROM users WHERE name LIKE '田中%';
  

• 全文検索エンジン（例: PostgreSQL のpg_trgm）の利用を検討。

4. OR 条件の使用

ORで複数条件を結合すると、インデックスが効かない場合があります。
例:

SELECT * FROM users WHERE age = 20 OR age = 30;

対策:

• IN句またはUNIONで書き換える。

  SELECT * FROM users WHERE age IN (20, 30);
  

5. NULL 値の検索

一部の DBMS ではIS NULLやIS NOT NULLがインデックスを無効化します。
例:

SELECT * FROM users WHERE email IS NULL;

対策:

• 非 NULL 制約とデフォルト値（例: 空文字）を設定し、IS NULL検索を回避。

  ALTER TABLE users ALTER COLUMN email SET DEFAULT '' NOT NULL;
  

6. 否定形の使用

<>やNOT INなどの否定条件はインデックスを無効化します。
例:

SELECT * FROM users WHERE name <> '田中';

対策:

• 等価条件（=）やIN句で代替する。

  SELECT * FROM users WHERE name IN ('佐藤', '鈴木');  -- 否定形を回避
  

7. 複合インデックスの順序不一致

複合インデックスを作成した列の順序と、クエリの条件順序が一致しない場合に発生します。
例:
インデックス(col1, col2)に対し、

SELECT * FROM table WHERE col2 = 100 AND col1 = 200;  -- 順序不一致

対策:

• インデックスの作成順序をクエリの条件順序と一致させる。
• カバリングインデックスを活用し、取得列をインデックスに含める。

8. 統計情報の不足やメンテナンス不足

データ更新後にインデックスの統計情報が古い場合、オプティマイザが非効率な実行計画を選択します。
対策:

• 定期的な統計情報の更新を実施。

  ANALYZE TABLE users;  -- MySQL例
  

• インデックスの再構築で断片化解消。

9. その他の注意点

• WITH句や副問い合わせ: 仮想テーブルではインデックスが効かないため、実テーブルで条件を指定。
• カーディナリティの低い列: 値の重複が多い列（例: 性別）はインデックス効果が薄い。代わりに複合インデックスを検討。

パフォーマンス確認方法

EXPLAINコマンドで実行計画を確認し、typeがALL（フルスキャン）かrange/ref（インデックス使用）かをチェック。

EXPLAIN SELECT * FROM users WHERE name = '田中';

インデックスが効かない場合に UI 側でできる対策

インデックスが効かない SQL は、DB 設計や SQL 自体の見直しが基本ですが、UI（ユーザーインターフェース）側でも工夫できるケースがあります。以下に主なパターンと UI での対策例をまとめます。

1. 検索条件の入力方法を制限する

• 中間一致・後方一致の LIKE 検索を禁止し、前方一致のみ許可する
  • 例：「%田中%」や「%田中」などの入力を UI で禁止し、「田中%」のみ許可するようにする
  • オートコンプリートやサジェスト機能で前方一致検索を促す

2. 検索条件の組み合わせを制御する

• OR 条件の多用を避ける UI 設計
  • 複数条件を AND で結合するように UI を設計し、OR 検索は極力避ける
  • 複数選択肢を IN 句に変換するような UI（チェックボックスや複数選択リスト）を用意する

3. 入力値の型やフォーマットを統一させる

• 数値・日付・文字列の型を正しく入力させる
  • 例：ID 検索フォームでは数字以外入力できないよう制限する
  • 日付検索ではカレンダー UI を用い、フォーマットのブレを防ぐ

4. NULL 検索を避ける UI 設計

• 「未入力」や「指定なし」を明示的な値（例：'-'や'未設定'）で選択させる
  • NULL による検索を UI で発生させないようにする

5. 不要な絞り込みや複雑な条件指定をさせない

• 多段階のフィルタや複雑な条件式を入力させない UI にする
  • 検索条件をシンプルに保つことで、インデックスが効きやすいクエリになる

6. 検索回数やデータ件数を制限する

• ページングや表示件数制限、サジェスト機能の活用
  • 一度に大量のデータを検索・表示させない
  • 入力途中でサジェストして、対象データを絞り込ませる

7. 非同期処理で UI のフリーズを防ぐ

• 検索処理を非同期で実行し、UI が固まらないようにする
  • 検索中はローディング表示やキャンセルボタンを設ける

データマートにおけるインデックス効率化対策

データマートは特定の分析目的に最適化されたデータベースですが、インデックス設計やクエリ最適化を誤るとパフォーマンス低下を招きます。以下に対策と運用上のポイントを整理します。

インデックスが効かない場合のデータマート対策

1. データロード時のインデックス無効化
ETL 処理中はインデックスを一時無効化し、ロード完了後に再構築することで効率化。

-- 例: PostgreSQL
ALTER TABLE sales_data DISABLE TRIGGER ALL;
-- ETL処理実行
ALTER TABLE sales_data ENABLE TRIGGER ALL;
REINDEX TABLE sales_data;

2. パーティショニングの適用
時間軸やカテゴリ別にテーブル分割。特定範囲のみスキャン可能に。

-- 例: 月次パーティショニング
CREATE TABLE sales_2025_06 PARTITION OF sales_data
FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');

3. カバリングインデックスの導入
頻繁にアクセスする列をインデックスに包含し、テーブルアクセスを削減。

CREATE INDEX idx_product_analysis ON sales_data (product_id)
INCLUDE (quantity, unit_price);

4. マテリアライズドビューの活用
事前集計済みデータを保持し、複雑クエリを回避。

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT product_id, SUM(quantity)
FROM sales_data
GROUP BY product_id;

5. 統計情報の自動更新設定
オプティマイザが適切な実行計画を選択できるよう定期更新。

-- PostgreSQL例
ALTER TABLE sales_data SET (autovacuum_enabled = on);

データマート運用のメリット

• 高速なクエリ応答：目的特化型設計により分析クエリが 10-100 倍高速化
• 低コスト構築：データウェアハウス比で構築コスト最大 70%削減
• 部門別最適化：営業/マーケティングなど部署固有の分析ニーズに即時対応
• DWH 負荷分散：基幹システムへのクエリ負荷を軽減

データマートのデメリットと注意点

▼ 主な課題

• データサイロ化リスク：部門別に乱立すると全社的なデータ統合が困難
• 複雑分析不向き：多角的分析が必要なケースでは柔軟性不足
• メンテナンスコスト：データマート増加に伴い運用負荷が指数関数的増加

▼ 重要対応策

▼ パフォーマンス監視のポイント

1. クエリ応答時間の 99 パーセンタイル値の継続的計測
2. データロード時間が SLA を超過していないか定期チェック
3. 未使用インデックス/重複インデックスの特定と削除

データマートは特定の分析タスクでは極めて有効ですが、全社的なデータ戦略と整合させることで初めて真価を発揮します。データガバナンス体制の構築と、インデックス設計を含む定期的なパフォーマンスチューニングが成功の鍵となります。

インデックスオンリースキャン対策と運用注意点

インデックスオンリースキャンが効かない場合の具体的な対策方法と、導入時の注意点を以下に整理します。

インデックスオンリースキャン有効化対策

1. カバリングインデックスの適切な設計

   • SELECT句・WHERE句・JOIN条件の全列をインデックスに包含

     CREATE INDEX idx_orders_covering ON orders (order_date)
     INCLUDE (total_amount);  -- SELECT句で使用する列を包含
     

   • インデックスキー順序をクエリ条件と一致させる

2. B ツリーインデックスの優先採用

   • PostgreSQL では B ツリーが常に対応（GiST/SP-GiST は部分対応、GIN は非対応）

3. MVCC 可視性問題への対応

   • 定期的なVACUUM実行で不要タプルを除去
   • インデックスにxminやctidを含めることで可視性解決

     CREATE INDEX idx_users_visibility ON users (id, ctid);
     

4. クエリの最適化

   • 参照列を最小化（SELECT * 禁止）
   • 暗黙的型変換を排除（例：数値列への文字列比較禁止）

5. パーティショニングの併用

   CREATE TABLE sales_2025_q2 PARTITION OF sales
   FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
   

運用上の重要注意点

▼ パフォーマンストレードオフ

▼ 設計時のチェックリスト

▼ 失敗パターンと回避策

• 列追加による機能喪失

  -- 危険: 新規列追加でカバリングインデックスが無効化
  SELECT product_id, price, new_column FROM sales;
  

  対策: インデックス再定義またはクエリ修正

• 更新頻度の高いテーブル

  • カバリングインデックスによる更新性能劣化（最大 40%低下）
    対策: バッチ処理時間帯の調整または非同期更新

• インデックス肥大化

  • 10 列を超える包含インデックスでストレージ使用量 2 倍以上増加のケース
    対策: 頻繁に参照される列のみ包含

効果検証方法

1. 実行計画の確認

   EXPLAIN ANALYZE SELECT product_id FROM sales;
   -- Index Only Scan の表示を確認
   

2. パフォーマンス比較

   条件	平均レスポンス	ヒープアクセス
   テーブルスキャン	150ms	10 万回
   インデックスオンリースキャン	22ms	0 回

3. モニタリング指標

   • pg_stat_user_indexes.idx_tup_read の増加傾向
   • pg_stat_all_tables.heap_blks_hit の減少率

総括

インデックスオンリースキャンは参照系クエリで最大 10 倍の高速化が可能ですが、以下の条件で効果が限定的です：

• 更新頻度が高いテーブル（1 分間隔以下の更新）
• 選択列数が多いクエリ（5 列以上）
• カーディナリティが極端に低い列（性別フラグ等）

導入時は「最小限の列包含」「B ツリー採用」「定期的なパフォーマンス検証」の 3 原則を遵守し、ストレージ容量と更新コストのバランスを最適化することが重要です。

手続型と宣言型の違い

手続型（プロシージャル、命令型）プログラミング

• 特徴
  手続型プログラミングは、「どうやって処理を進めるか（How）」を細かく記述するスタイルです。
  プログラムは一連の命令（ステートメント）や手順（プロシージャ、サブルーチン）の集合で構成され、状態の変化や副作用を伴いながら処理が進みます。
  例としては、C 言語、BASIC、Pascal、FORTRAN などが挙げられます。

• 例
  「ラーメンを作る」場合、麺を茹でる → スープを作る → 具材を炒める → 盛り付ける…といった手順をすべて順番に指示するイメージです。

• メリット・デメリット

  • 実行手順を細かく制御できる
  • 状態管理や副作用が多く、複雑になりやすい

宣言型（デクララティブ）プログラミング

• 特徴
  宣言型プログラミングは、「何を実現したいか（What）」を記述するスタイルです。
  どのように処理するか（手順）はシステムや言語処理系に任せ、開発者は「目的」や「条件」だけを宣言します。
  SQL や HTML、正規表現、関数型言語（Haskell など）が代表例です。

• 例
  「ラーメンを作る」場合、「ネギラーメンください！」と注文するだけで、作り方は店側（システム）が最適に判断します。

• メリット・デメリット

  • コードが簡潔で直感的
  • 実行方法を意識せずに済むため保守性が高い
  • 複雑な制御や細かい最適化が難しい場合もある

まとめ表

SQL はどちら？

SQL は「宣言型言語」の代表例です。
「どのようにデータを取得するか」ではなく、「どんなデータが欲しいか」を記述し、実際の処理手順や最適化は DBMS が自動で行います。

SQL のパラレル実行とは

SQL のパラレル実行（並列実行）は、1 つの SQL クエリを複数のプロセスやスレッドに分散し、同時に処理することで、クエリ全体の実行速度を向上させる仕組みです。大量データの集計や分析、全表スキャンなど、重い処理で特に効果を発揮します。

パラレル実行の基本的な仕組み

• タスクの分割
  クエリ実行時、処理内容（例：テーブルスキャン、結合、集計など）が複数のプロセスやスレッドに分割されます。それぞれがデータの一部を担当し、同時に処理を進めます。

• コーディネータとワーカー
  通常は「コーディネータ」と呼ばれるプロセスが全体を管理し、複数の「パラレルサーバー」や「ワーカープロセス」が実際の処理を担当します。コーディネータは結果の集約や最終処理も担います[1][2][4]。

• 処理の流れ例

  1. クエリ解析時にパラレル実行が有効か判定
  2. コーディネータがワーカーを起動
  3. ワーカーがデータの一部を並列で処理
  4. コーディネータが各ワーカーの結果を集約し、最終結果を返却

パラレル実行の主な対象

• テーブルスキャン
  テーブルを複数の範囲に分割し、それぞれを同時にスキャン

• 結合処理
  複数のテーブルの結合を分割して並列実行

• 集計処理
  データを分割して各ワーカーで集計し、最後に合算

パラレル実行のメリット・デメリット

• メリット

  • クエリの処理速度が大幅に向上する（特に大量データ処理時）
  • 複数 CPU やノードのリソースを最大限活用できる

• デメリット

  • 並列化のオーバーヘッドが発生するため、小規模データや単純なクエリでは逆効果の場合もある
  • リソース競合や同時実行数の制御が必要

データベースごとのパラレル実行例

注意点

• パラレル実行は大量データ処理や全表スキャン、複雑な集計クエリで効果的
• 小規模なデータや単純なクエリでは、並列化のオーバーヘッドで逆に遅くなる場合がある
• システム全体の CPU やメモリなどリソース消費が増えるため、同時実行クエリ数やリソース管理に注意が必要

まとめ

SQL のパラレル実行は、クエリ処理を複数プロセス・スレッドに分散し、処理速度を向上させる技術です。大量データの分析や集計に有効ですが、適用にはリソース管理やクエリ特性の理解が重要です。

JSON_CONTAINS 関数の詳細な使い方

JSON_CONTAINS関数は、MySQL で JSON 型のデータを検索するための関数です。指定した JSON データ（target）の中に、特定の値やオブジェクト（candidate）が含まれているかどうかを判定し、含まれていれば 1、含まれていなければ 0 を返します。

構文

JSON_CONTAINS(target, candidate[, path])

• target: 検索対象となる JSON ドキュメント（JSON 型カラムや JSON 文字列）
• candidate: 検索したい値やオブジェクト（JSON 形式で指定）
• path（省略可）: 検索対象のパス（JSONPath 形式で指定）

基本的な使い方

配列内に値が存在するか調べる

SELECT JSON_CONTAINS('[1, 2, 3, 4]', '1');
-- 結果: 1（1が含まれているため）

SELECT JSON_CONTAINS('[1, 2, 3, 4]', '0');
-- 結果: 0（0は含まれていないため）

オブジェクト内に特定の値が存在するか調べる

SELECT JSON_CONTAINS(
  '{"employees": [{"name": "John", "age": 30}, {"name": "Jane", "age": 35}]}',
  '"John"',
  '$.employees[*].name'
);
-- 結果: 1（employees配列内のnameに"John"が存在するため）

パスを指定して検索

パスを指定することで、JSON ドキュメントの特定の部分だけを検索対象にできます。

SELECT JSON_CONTAINS('{"a": [1, 2, 3]}', '2', '$.a');
-- 結果: 1（配列aの中に2が含まれているため）

注意点

• candidateやtargetは、JSON 形式の文字列で指定する必要があります。数値や文字列の場合も、ダブルクォートで囲む必要があります。
• パス指定を省略した場合、target 全体が検索対象になります。
• 配列やオブジェクトの部分一致も可能です。

実用例

テーブルの JSON カラムに対して検索

SELECT *
FROM users
WHERE JSON_CONTAINS(roles, '"admin"');

この例では、roles カラム（JSON 配列）に"admin"という値が含まれているレコードを抽出します。

まとめ

• JSON_CONTAINS は、JSON 型カラムに特定の値やオブジェクトが含まれているかどうかを判定する関数です。
• パスを指定することで、JSON の一部のみを検索対象にできます。
• 結果は 1（含まれる）または 0（含まれない）で返されます。

JSON 型のデータを柔軟に検索したい場合に非常に便利な関数です。

JSON_TABLE の詳細な仕組みと実践活用（具体例を用いた解説）

この SQL クエリは、history_tableの JSON 型カラムdataをリレーショナル形式に変換する処理です。実際のコードを基に、JSON_TABLE の動作原理と重要なポイントを段階的に解説します。

クエリの構造分解

SELECT *
FROM
  history_table,
  JSON_TABLE(
    data,
    '$' COLUMNS(
      `StatusFLG` CHAR(1) PATH '$.StatusFLG',
      `change_ymd` DATE PATH '$.ChangeYMD',
      `change_hms` TIME PATH '$.ChangeHMS',
      `name` CHAR(30) PATH '$.BumonName',
      `code` CHAR(4) PATH '$.CompanyCD'
    )
  ) AS jt

1. データソース指定

• data: 処理対象の JSON カラム
• '$': JSON ドキュメントのルートを指定

2. カラム定義

実行時の内部処理フロー

1. JSON データのパース

   {
     "StatusFLG": "1",
     "ChangeYMD": "2023-04-01",
     "ChangeHMS": "15:30:00",
     "BumonName": "営業部",
     "CompanyCD": "0001"
   }
   

   → この JSON オブジェクトをパース

2. カラムへのマッピング

   StatusFLG: '1' → CHAR(1)
   change_ymd: '2023-04-01' → DATE
   change_hms: '15:30:00' → TIME
   name: '営業部' → CHAR(30)
   code: '0001' → CHAR(4)
   

3. 仮想テーブル生成

   StatusFLG	change_ymd	change_hms	name	code
   1	2023-04-01	15:30:00	営業部	0001

重要な技術ポイント

1. 暗黙の結合（CROSS JOIN）

• history_tableと JSON_TABLE の結果を暗黙的に結合
• 元テーブルの 1 行に対し、JSON_TABLE で生成した 1 行が対応

2. 型変換の挙動

• DATE/TIME 型: 文字列から自動変換
• 数値型: INT や DECIMAL に明示的にキャスト可能
• 文字列型: COLLATE で照合順序を指定可能

3. パス解決の仕様

• 存在しないパス: NULL が返される
• ネスト構造: $.parent.child でアクセス可能
• 配列要素: $.item で配列の最初の要素を取得

よくあるエラーと対策

エラー例 1: データ型不一致

{"ChangeYMD": "2023/04/01"} → DATE型変換失敗

→ 対策: STR_TO_DATE 関数で明示的に変換

エラー例 2: 文字列長超過

{"BumonName": "国際事業開発部"} → CHAR(30)を超過

→ 対策: VARCHAR(255)など可変長型を使用

パフォーマンス最適化手法

1. 必要なカラムのみ選択

SELECT jt.StatusFLG, jt.change_ymd -- 全カラム(*)の使用を避ける

2. WHERE 句で早期フィルタリング

WHERE JSON_EXTRACT(data, '$.StatusFLG') = '1'

3. 生成カラムにインデックス作成

ALTER TABLE history_table ADD COLUMN status_flg CHAR(1)
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.StatusFLG')));
CREATE INDEX idx_status ON history_table(status_flg);

適用ケース例

1. 履歴データ分析: JSON 形式の変更履歴を集計
2. レポート生成: JSON データを定型フォーマットで出力
3. 外部システム連携: リレーショナル形式が必要な API へのデータ提供

まとめ：JSON_TABLE 活用のベストプラクティス

• 必要最小限のカラムを定義し、パフォーマンス低下を防ぐ
• 適切なデータ型を指定して暗黙的型変換を回避
• バージョン確認: MySQL 8.0.4 以降で利用可能
• EXPLAIN で実行計画を確認し、インデックス活用を検討
• エラーハンドリング: 存在しないパスや型不一致を想定した設計

この例のように JSON_TABLE を効果的に使用することで、JSON データとリレーショナルデータをシームレスに連携させ、複雑なデータ構造でも柔軟に処理できます。

MySQL の JSON 関連関数の解説

MySQL では JSON 型のカラムを柔軟に操作するための関数が多数用意されています。ここでは、代表的な 3 つの関数について用途や使い方を分かりやすくまとめます。

1. JSON_TABLE

概要

JSON_TABLEは、JSON データをリレーショナル（表形式）データとして展開するための関数です。JSON 型カラムの中身を SQL のテーブルカラムとして扱いたい場合に使います。

主な用途

• JSON データから複数の値を一度に抽出し、仮想的なテーブルとして利用
• そのまま JOIN や WHERE 句、集計などの SQL 処理に利用可能

例

SELECT *
FROM users,
  JSON_TABLE(
    users.profile,
    '$'
    COLUMNS(
      name VARCHAR(50) PATH '$.name',
      age INT PATH '$.age'
    )
  ) AS jt
;

この例では、usersテーブルのprofile（JSON 型）からnameとageを抽出し、SQL のカラムとして利用しています。

2. JSON_EXTRACT

概要

JSON_EXTRACTは、JSON ドキュメントから特定の値を取得する関数です。JSON の特定のパスを指定して、値を取り出します。

主な用途

• JSON カラムから単一または複数の値を直接取得
• WHERE 句や SELECT 句での条件抽出に便利

例

SELECT JSON_EXTRACT(profile, '$.name') AS user_name
FROM users;

この例では、profileカラムの JSON からnameの値だけを抽出しています。

3. JSON_KEYS

概要

JSON_KEYSは、JSON オブジェクトのキー（フィールド名）の一覧を配列として返す関数です。どんなフィールドがあるか動的に知りたいときに使います。

主な用途

• JSON オブジェクトの構造を調査
• 動的なカラム名の取得やループ処理などに利用

例

SELECT JSON_KEYS(profile) AS keys
FROM users;

この例では、profileカラムの JSON オブジェクトに含まれる全てのキー名を配列で取得します。

まとめ

これらの関数を組み合わせることで、MySQL で JSON データを柔軟かつ効率的に扱うことができます。

「TEMP 落ち」とは

「TEMP 落ち」とは、データベースで大量のデータを扱う際や複雑なソート・集計・結合処理などを行う際に、メモリ上で処理しきれなくなった一時データがディスク上の一時領域（TEMP 領域、一時表領域）に退避される現象、またはその一時領域すら使い切ってエラーになる状態を指します。

詳細

• 通常、ソートや集計などの一時的なデータ処理はメモリ上で行われます。
• しかし、データ量が多かったり、メモリに収まりきらない場合、その一時データはディスク上の TEMP 領域（一時表領域）に書き出されます。
• このディスクへの退避処理自体を「TEMP 落ち」と呼ぶことが多いです。
• TEMP 領域の容量も使い切ると、**「一時表領域が枯渇」**というエラー（例：Oracle の ORA-01652、一時テーブルがいっぱいなど）が発生し、SQL の処理が失敗します。

例

• 大量データの SELECT や ORDER BY、GROUP BY、JOIN、DISTINCT、サブクエリなどで発生しやすいです。
• 例えば、MySQL や Oracle、SQL Server など主要な DBMS で共通して見られる現象です。

TEMP 落ちが発生した場合の影響

• クエリの実行速度が大幅に低下します（ディスク I/O が発生するため）。
• TEMP 領域が枯渇すると、該当クエリだけでなく、他の処理にも影響が及び、最悪の場合データベース全体の処理が停止することもあります。

対策

• SQL チューニング（不要な全件検索や複雑なソート・集計処理の見直し）
• TEMP 領域（一時表領域）の拡張
• 必要に応じて専用の TEMP 領域をユーザーや処理ごとに割り当てる[1][3][5][6]

まとめ
TEMP 落ちとは、メモリ不足により一時データがディスクの TEMP 領域へ退避される現象や、その領域が枯渇してエラーになる状態のことです。SQL のパフォーマンスや安定性に大きく影響するため、発生時は SQL や DB の設定を見直すことが重要です。

データベースの結合アルゴリズムの 3 種類

データベース管理システム（DBMS）は、テーブル結合処理を最適化するために以下の 3 つの主要アルゴリズムを活用します。各アルゴリズムの特徴と最適な使用ケースを解説します。

1. ネステッドループ結合（Nested Loop Join）

処理フロー

1. 外部テーブル（駆動表）から 1 行取得
2. 取得行の結合キー値で内部テーブルをスキャン
3. 条件一致する行を結合して出力
4. 外部テーブルの全行でこの処理を繰り返し

特徴

• 外部テーブルが小規模で内部テーブルにインデックスがある場合に高速
• 等価結合・非等価結合の両方に対応
• 時間計算量：O(M×N)（M:外部表行数, N:内部表行数）

最適ケース
外部テーブルが極小（例：10 行）かつ内部テーブルの結合キーにインデックスがある場合

2. ソートマージ結合（Sort-Merge Join）

処理フロー

1. 両テーブルを結合キーで独立にソート
2. ソート結果を先頭から走査
3. キー値が一致する行を結合（マージ処理）

特徴

• ソート済みデータやクラスタ化インデックスで効率化
• 不等号結合（>, <）にも対応可能
• メモリ不足時はディスクスピルが発生し遅延

最適ケース

• 事前ソート済みデータ
• 大規模テーブル同士の結合
• 非等価結合が必要な場合

3. ハッシュ結合（Hash Join）

処理フロー

1. 小さいテーブル（ビルド表）の結合キーでハッシュテーブル作成
2. 大きいテーブル（プローブ表）を走査
3. 各行のハッシュ値を元にビルド表と結合

特徴