MySQL - SELECT

提供: MochiuWiki : SUSE, EC, PCB

📢 Webサイト閉鎖と移転のお知らせ
このWebサイトは2026年9月に閉鎖いたします。
新しい記事は移転先で追加しております。（旧サイトでは記事を追加しておりません）

概要

SELECT文は、MySQLのテーブルからデータを取得するための最も基本的かつ最も使用頻度の高いSQL文である。

単一テーブルからの単純な取得だけでなく、WHERE 句による条件指定、ORDER BY による並べ替え、GROUP BY によるグループ化、JOIN によるテーブル結合、集約関数による計算等、多様なデータ操作が可能である。

MySQL 8.0以降では、ウィンドウ関数や共通テーブル式 (CTE) が追加され、複雑な分析クエリの記述が容易になった。
再帰CTE (再帰的共通テーブル式) により、階層データの処理も可能である。

SELECT文のパフォーマンス最適化には、インデックスの活用、EXPLAIN 文による実行計画の分析、クエリの書き方の工夫が重要である。
特に大規模データベースにおいては、適切なインデックス設計とクエリチューニングがシステムの性能を大きく左右する。

サブクエリを使用した複雑な条件指定も可能であるが、サブクエリの詳細を知りたい場合は、MySQL - 副問い合わせのページを参照すること。


基本構文

SELECT文の完全な構文

SELECT文の完全な構文を以下に示す。

 SELECT
    [ALL | DISTINCT | DISTINCTROW]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [FROM table_references
        [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
        [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
        [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [INTO OUTFILE 'file_name' ... | INTO DUMPFILE 'file_name' | INTO var_name [, var_name] ...]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]]


基本的な使用例を以下に示す。

 # 全カラムを取得
 SELECT * FROM users;
 
 # 特定のカラムを取得
 SELECT id, name, email FROM users;
 
 # 条件を指定してデータを取得
 SELECT name, email FROM users WHERE status = 'active';


カラムの指定

SELECT文では、取得するカラムを複数の方法で指定できる。

 # 全カラムを取得
 SELECT * FROM users;
 
 # 特定のテーブルの全カラムを取得
 SELECT users.* FROM users;
 
 # 特定のカラムを指定
 SELECT id, name, email FROM users;
 
 # テーブル名を明示
 SELECT users.id, users.name FROM users;
 
 # 計算式を指定
 SELECT price * quantity AS total FROM order_items;
 
 # 関数を使用
 SELECT UPPER(name), LENGTH(email) FROM users;


エイリアス (AS句)

AS 句を使用して、カラムやテーブルに別名を付けることができる。

 # カラムにエイリアスを付ける
 SELECT name AS user_name, email AS user_email FROM users;
 
 # ASを省略することも可能
 SELECT name user_name, email user_email FROM users;
 
 # テーブルにエイリアスを付ける
 SELECT u.id, u.name FROM users AS u;
 
 # 計算式にエイリアスを付ける
 SELECT price * quantity AS total_price FROM order_items;
 
 # 関数の結果にエイリアスを付ける
 SELECT COUNT(*) AS total_users FROM users;


DISTINCT

DISTINCT キーワードを使用して、重複行を除外することができる。

 # 重複を除外してカラムを取得
 SELECT DISTINCT status FROM users;
 
 # 複数カラムの組み合わせで重複を除外
 SELECT DISTINCT status, role FROM users;
 
 # DISTINCTROWはDISTINCTと同義
 SELECT DISTINCTROW status FROM users;


※注意
DISTINCTは全ての指定カラムの組み合わせに対して適用される。
一部のカラムにのみDISTINCTを適用することはできない。


WHERE句

基本的な条件指定

WHERE 句を使用して、取得するデータの条件を指定する。

下表に、基本的な比較演算子を示す。

比較演算子一覧
演算子 説明
= 等しい
<>, != 等しくない
< より小さい
> より大きい
<= 以下
>= 以上
<=> NULLセーフ等号 (NULL同士も等しいと判定)


使用例を以下に示す。

 # 等価条件
 SELECT * FROM users WHERE id = 100;
 
 # 不等号条件
 SELECT * FROM products WHERE price > 1000;
 
 # 不等価条件
 SELECT * FROM orders WHERE status <> 'completed';
 
 # NULLセーフ等号
 SELECT * FROM users WHERE deleted_at <=> NULL;


複数条件の指定

AND、OR、NOT、XOR を使用して、複数の条件を組み合わせることができる。

 # AND条件 (両方の条件を満たす行を取得)
 SELECT * FROM users
    WHERE status = 'active'
       AND role = 'admin';
 
 # OR条件 (いずれかの条件を満たす行を取得)
 SELECT * FROM users
    WHERE role = 'admin'
       OR role = 'moderator';
 
 # NOT条件 (条件を満たさない行を取得)
 SELECT * FROM users
    WHERE NOT status = 'deleted';
 
 # XOR条件 (いずれか一方のみが真の行を取得)
 SELECT * FROM users
    WHERE (status = 'active') XOR (role = 'admin');
 
 # 複数条件の組み合わせ (括弧で優先順位を明示)
 SELECT * FROM orders
    WHERE (status = 'pending' OR status = 'processing')
       AND created_at >= '2024-01-01';


LIKE句 (パターンマッチング)

LIKE 句を使用して、文字列のパターンマッチングを行う。

ワイルドカードの種類を下表に示す。

ワイルドカード一覧
ワイルドカード 説明
% 0個以上の任意の文字に一致
_ 1個の任意の文字に一致


使用例を以下に示す。

 # 特定の文字列で始まる
 SELECT * FROM users WHERE email LIKE 'admin%';
 
 # 特定の文字列で終わる
 SELECT * FROM users WHERE email LIKE '%@example.com';
 
 # 特定の文字列を含む
 SELECT * FROM products WHERE name LIKE '%phone%';
 
 # 3文字目がaである4文字の名前
 SELECT * FROM users WHERE name LIKE '__a_';
 
 # NOT LIKEで否定
 SELECT * FROM users WHERE email NOT LIKE '%@spam.com';
 
 # エスケープ文字の使用 (リテラルとして%や_を検索)
 SELECT * FROM products WHERE description LIKE '%\%%';  # %を含む
 SELECT * FROM products WHERE code LIKE '%\_test%';     # _testを含む


IN句とBETWEEN句

IN 句と BETWEEN 句を使用して、値の範囲やリストを指定する。

 # IN句 (リストに含まれる値)
 SELECT * FROM users WHERE id IN (1, 2, 3, 5, 8);
 
 # NOT IN句 (リストに含まれない値)
 SELECT * FROM users WHERE status NOT IN ('deleted', 'banned');
 
 # BETWEEN句 (範囲指定、両端を含む)
 SELECT * FROM products WHERE price BETWEEN 1000 AND 5000;
 
 # NOT BETWEEN句
 SELECT * FROM orders WHERE created_at NOT BETWEEN '2024-01-01' AND '2024-12-31';
 
 # 文字列でのBETWEEN
 SELECT * FROM users WHERE name BETWEEN 'A' AND 'C';
 
 # 日付でのBETWEEN
 SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';


NULL値の判定

IS NULL と IS NOT NULL を使用して、NULL値を判定する。

 # NULL値を持つ行を取得
 SELECT * FROM users WHERE deleted_at IS NULL;
 
 # NULL値でない行を取得
 SELECT * FROM users WHERE email IS NOT NULL;
 
 # 複数カラムのNULL判定
 SELECT * FROM users
    WHERE deleted_at IS NULL
       AND last_login IS NOT NULL;


※注意
NULL値の比較には = や <> を使用できない。
必ず、IS NULL または IS NOT NULL を使用する必要がある。

 # 正しい例
 SELECT * FROM users WHERE deleted_at IS NULL;
 
 # 誤った例 (NULLは取得されない)
 SELECT * FROM users WHERE deleted_at = NULL;



ORDER BY句

ORDER BY 句を使用して、取得したデータを並べ替えることができる。

 # 昇順でソート (デフォルト)
 SELECT * FROM users ORDER BY name ASC;
 
 # ASCは省略可能
 SELECT * FROM users ORDER BY name;
 
 # 降順でソート
 SELECT * FROM users ORDER BY created_at DESC;
 
 # 複数カラムでソート
 SELECT * FROM users ORDER BY status ASC, name ASC;
 
 # カラムごとに異なるソート順
 SELECT * FROM orders ORDER BY status ASC, created_at DESC;
 
 # カラム位置でソート (1から始まる)
 SELECT name, email, created_at FROM users ORDER BY 3 DESC;
 
 # 計算式でソート
 SELECT name, price, quantity FROM order_items
    ORDER BY price * quantity DESC;
 
 # 関数の結果でソート
 SELECT name FROM users ORDER BY LENGTH(name) ASC;
 
 # FIELD関数でカスタムソート順を指定
 SELECT * FROM orders
    ORDER BY FIELD(status, 'pending', 'processing', 'completed', 'cancelled');


※NULL値のソート順
ASC (昇順) の場合、NULL値は先頭に配置される。
DESC (降順) の場合、NULL値は末尾に配置される。


LIMIT句とOFFSET

LIMIT 句を使用して、取得する行数を制限できる。
OFFSET を使用して、開始位置を指定できる。

 # 最初の10行を取得
 SELECT * FROM users LIMIT 10;
 
 # 11行目から10行を取得 (OFFSET構文)
 SELECT * FROM users LIMIT 10 OFFSET 10;
 
 # 11行目から10行を取得 (カンマ構文)
 SELECT * FROM users LIMIT 10, 10;
 
 # ページネーション例 (1ページ20件、3ページ目を取得)
 SELECT * FROM products LIMIT 20 OFFSET 40;
 
 # ORDER BYとの組み合わせ (最新の5件を取得)
 SELECT * FROM orders ORDER BY created_at DESC LIMIT 5;
 
 # 最も高価な商品トップ10
 SELECT * FROM products ORDER BY price DESC LIMIT 10;


※注意
LIMIT <オフセット値>, <行数> の構文では、最初の値がOFFSET、2番目の値が取得行数である。
LIMIT <行数> OFFSET <オフセット値> の構文では、順序が逆になるため注意が必要である。


GROUP BY句とHAVING句

GROUP BY句

GROUP BY 句を使用して、指定したカラムの値でデータをグループ化できる。
集約関数と組み合わせて使用することで、グループごとの集計が可能である。

 # 単一カラムでグループ化
 SELECT status, COUNT(*) FROM users GROUP BY status;
 
 # 複数カラムでグループ化
 SELECT status, role, COUNT(*) FROM users
    GROUP BY status, role;
 
 # 日付でグループ化
 SELECT DATE(created_at) AS date, COUNT(*) AS count
    FROM orders
    GROUP BY DATE(created_at);
 
 # 年月でグループ化
 SELECT YEAR(created_at) AS year, MONTH(created_at) AS month, COUNT(*)
    FROM orders
    GROUP BY YEAR(created_at), MONTH(created_at);
 
 # WITH ROLLUPで小計と総計を含める
 SELECT status, role, COUNT(*) FROM users
    GROUP BY status, role WITH ROLLUP;


※注意
GROUP BYを使用する場合、SELECT句には集約関数またはGROUP BY句に指定したカラムのみを含めることができる。
ただし、MySQL 5.7.5以降では、sql_mode=ONLY_FULL_GROUP_BY が有効である。

HAVING句

HAVING 句を使用して、グループ化後のデータをフィルタリングできる。
WHERE句がグループ化前のフィルタであるのに対し、HAVING句はグループ化後のフィルタである。

 # グループごとの件数でフィルタ
 SELECT status, COUNT(*) AS count FROM users
    GROUP BY status
    HAVING count > 10;
 
 # 集約関数の結果でフィルタ
 SELECT category_id, AVG(price) AS avg_price FROM products
    GROUP BY category_id
    HAVING AVG(price) > 1000;
 
 # 複数条件を指定
 SELECT status, role, COUNT(*) AS count FROM users
    GROUP BY status, role
    HAVING count > 5 AND status = 'active';
 
 # WHERE句とHAVING句の組み合わせ
 SELECT status, COUNT(*) AS count FROM users
    WHERE deleted_at IS NULL
    GROUP BY status
    HAVING count >= 100;



集約関数

集約関数を使用して、データの集計や計算を行うことができる。

主要な集約関数を下表に示す。

主要な集約関数
関数 説明
COUNT(*) 全行数をカウント (NULLを含む)
COUNT(expr) NULLでない値の数をカウント
COUNT(DISTINCT expr) 重複を除外してカウント
SUM(expr) 合計値を計算
AVG(expr) 平均値を計算
MIN(expr) 最小値を取得
MAX(expr) 最大値を取得
GROUP_CONCAT(expr) 値を連結した文字列を取得
STD(expr), STDDEV(expr) 標準偏差を計算
VARIANCE(expr) 分散を計算


使用例を以下に示す。

 # 行数をカウント
 SELECT COUNT(*) FROM users;
 
 # NULLでない値をカウント
 SELECT COUNT(email) FROM users;
 
 # 重複を除外してカウント
 SELECT COUNT(DISTINCT status) FROM users;
 
 # 合計値を計算
 SELECT SUM(price) FROM order_items;
 
 # 平均値を計算
 SELECT AVG(price) FROM products;
 
 # 最小値と最大値
 SELECT MIN(price), MAX(price) FROM products;
 
 # GROUP_CONCATで値を連結
 SELECT user_id, GROUP_CONCAT(product_name) AS products
    FROM orders
    GROUP BY user_id;
 
 # GROUP_CONCATのオプション
 SELECT user_id, GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ')
    FROM orders
    GROUP BY user_id;
 
 # 複数の集約関数を同時に使用
 SELECT
    COUNT(*) AS total,
    SUM(price) AS total_price,
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price
    FROM products;



JOINによる結合

INNER JOIN

INNER JOIN を使用して、両方のテーブルで一致する行のみを取得する。

 # 基本的なINNER JOIN
 SELECT users.name, orders.order_date
    FROM users
    INNER JOIN orders ON users.id = orders.user_id;
 
 # テーブルエイリアスを使用
 SELECT u.name, o.order_date
    FROM users AS u
    INNER JOIN orders AS o ON u.id = o.user_id;
 
 # 複数条件のJOIN
 SELECT u.name, o.order_date
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id AND o.status = 'completed';
 
 # 3つのテーブルをJOIN
 SELECT u.name, o.order_date, oi.product_name
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    INNER JOIN order_items oi ON o.id = oi.order_id;
 
 # INNER JOINはJOINと省略可能
 SELECT u.name, o.order_date
    FROM users u
    JOIN orders o ON u.id = o.user_id;


LEFT JOIN

LEFT JOIN を使用して、左テーブルの全行と、右テーブルの一致する行を取得する。
右テーブルに一致する行がない場合、NULL値が返される。

 # 基本的なLEFT JOIN
 SELECT users.name, orders.order_date
    FROM users
    LEFT JOIN orders ON users.id = orders.user_id;
 
 # 注文がないユーザを取得
 SELECT users.name
    FROM users
    LEFT JOIN orders ON users.id = orders.user_id
    WHERE orders.user_id IS NULL;
 
 # 複数テーブルのLEFT JOIN
 SELECT u.name, o.order_date, oi.product_name
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    LEFT JOIN order_items oi ON o.id = oi.order_id;
 
 # LEFT OUTER JOINと同義
 SELECT u.name, o.order_date
    FROM users u
    LEFT OUTER JOIN orders o ON u.id = o.user_id;


RIGHT JOIN

RIGHT JOIN を使用して、右テーブルの全行と、左テーブルの一致する行を取得する。

 # 基本的なRIGHT JOIN
 SELECT users.name, orders.order_date
    FROM users
    RIGHT JOIN orders ON users.id = orders.user_id;
 
 # RIGHT OUTER JOINと同義
 SELECT u.name, o.order_date
    FROM users u
    RIGHT OUTER JOIN orders o ON u.id = o.user_id;


※注意
RIGHT JOINは、LEFT JOINとテーブル順序を逆にすることで同じ結果が得られる。
一般的には、LEFT JOINの方が使用頻度が高い。

CROSS JOIN

CROSS JOIN を使用して、2つのテーブルの直積 (全組み合わせ) を取得する。

 # 基本的なCROSS JOIN
 SELECT users.name, products.product_name
    FROM users
    CROSS JOIN products;
 
 # カンマ区切りの旧構文
 SELECT users.name, products.product_name
    FROM users, products;
 
 # 条件を付けることも可能
 SELECT u.name, p.product_name
    FROM users u
    CROSS JOIN products p
    WHERE p.price > 1000;


自己結合

同じテーブルを複数回参照する自己結合を行うことができる。

 # 同じテーブルを結合 (従業員と上司の関係)
 SELECT e.name AS employee, m.name AS manager
    FROM employees e
    INNER JOIN employees m ON e.manager_id = m.id;
 
 # 同じカテゴリの商品を取得
 SELECT p1.product_name, p2.product_name
    FROM products p1
    INNER JOIN products p2
       ON p1.category_id = p2.category_id
       AND p1.id < p2.id;



UNION

UNION を使用して、複数のSELECT文の結果を結合できる。

 # 基本的なUNION (重複を除外)
 SELECT name FROM users
 UNION
 SELECT name FROM customers;
 
 # UNION ALL (重複を許可、高速)
 SELECT name FROM users
 UNION ALL
 SELECT name FROM customers;
 
 # カラム名やデータ型が一致する必要がある
 SELECT id, name, 'user' AS type FROM users
 UNION
 SELECT id, name, 'customer' AS type FROM customers;
 
 # ORDER BYは最後のSELECTにのみ指定
 SELECT name, created_at FROM users
 UNION
 SELECT name, created_at FROM customers
 ORDER BY created_at DESC;
 
 # 括弧を使用して個別にORDER BYを指定
 (SELECT name FROM users ORDER BY name LIMIT 10)
 UNION
 (SELECT name FROM customers ORDER BY name LIMIT 10);


※注意
UNIONで結合するSELECT文は、同じカラム数とデータ型の互換性が必要である。
カラム名は最初のSELECT文のものが使用される。


WITH句 (共通テーブル式)

基本的なCTE

WITH 句を使用して、共通テーブル式 (CTE: Common Table Expression) を定義できる。
MySQL 8.0以降で使用可能である。

 # 基本的なCTE
 WITH active_users AS (
    SELECT id, name, email
       FROM users
       WHERE status = 'active'
 )
 SELECT * FROM active_users WHERE email LIKE '%@example.com';
 
 # 複数のCTEを定義
 WITH
    active_users AS (
       SELECT id, name FROM users WHERE status = 'active'
    ),
    recent_orders AS (
       SELECT user_id, order_date FROM orders WHERE order_date >= '2024-01-01'
    )
 SELECT u.name, o.order_date
    FROM active_users u
    INNER JOIN recent_orders o ON u.id = o.user_id;
 
 # CTEを複数回参照
 WITH user_stats AS (
    SELECT status, COUNT(*) AS count FROM users GROUP BY status
 )
 SELECT * FROM user_stats WHERE count > 100
 UNION
 SELECT * FROM user_stats WHERE status = 'admin';


再帰CTE

WITH RECURSIVE を使用して、再帰的な処理を行うことができる。
階層データの処理に有用である。

 # 1から10までの数値を生成
 WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
 )
 SELECT * FROM numbers;
 
 # 組織の階層構造を取得
 WITH RECURSIVE org_hierarchy AS (
    # 非再帰部分 (ルート要素)
    SELECT id, name, manager_id, 1 AS level
       FROM employees
       WHERE manager_id IS NULL
 
    UNION ALL
 
    # 再帰部分
    SELECT e.id, e.name, e.manager_id, oh.level + 1
       FROM employees e
       INNER JOIN org_hierarchy oh ON e.manager_id = oh.id
 )
 SELECT * FROM org_hierarchy ORDER BY level, id;
 
 # カテゴリの階層構造を取得
 WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, name AS path
       FROM categories
       WHERE parent_id IS NULL
 
    UNION ALL
 
    SELECT c.id, c.name, c.parent_id, CONCAT(ct.path, ' > ', c.name)
       FROM categories c
       INNER JOIN category_tree ct ON c.parent_id = ct.id
 )
 SELECT * FROM category_tree;


※注意
再帰CTEには、無限ループを防ぐための再帰深度制限がある。
デフォルトは1000で、cte_max_recursion_depth変数で変更可能である。

 # 再帰深度の上限を変更
 SET SESSION cte_max_recursion_depth = 10000;



ウィンドウ関数

基本構文

ウィンドウ関数を使用して、行のグループに対する計算を行うことができる。
MySQL 8.0以降で使用可能である。

ウィンドウ関数の基本構文を以下に示す。

 関数 OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression [ASC | DESC]]
    [frame_clause]
 )


基本的な使用例を以下に示す。

 # 全行に対して連番を付ける
 SELECT id, name, ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM users;
 
 # グループごとに連番を付ける
 SELECT id, name, status,
    ROW_NUMBER() OVER (PARTITION BY status ORDER BY id) AS row_num
    FROM users;
 
 # 名前付きウィンドウを定義
 SELECT id, name, status,
    ROW_NUMBER() OVER w AS row_num,
    RANK() OVER w AS rank_num
    FROM users
    WINDOW w AS (PARTITION BY status ORDER BY created_at DESC);


ランキング関数

ウィンドウ関数を使用したランキングを行うことができる。

主要なランキング関数を下表に示す。

ランキング関数
関数 説明
ROW_NUMBER() 連番を付ける (同順位なし)
RANK() 順位を付ける (同順位あり、次の順位は飛ばす)
DENSE_RANK() 順位を付ける (同順位あり、次の順位は飛ばさない)
NTILE(n) n個のグループに分割して番号を付ける
PERCENT_RANK() 相対順位をパーセンテージで返す (0から1)
CUME_DIST() 累積分布を返す


使用例を以下に示す。

 # 価格の高い順に順位を付ける
 SELECT
    product_name,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
    RANK() OVER (ORDER BY price DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank_num
    FROM products;
 
 # カテゴリごとに順位を付ける
 SELECT
    category_id,
    product_name,
    price,
    RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank_in_category
    FROM products;
 
 # データを4つのグループに分割
 SELECT
    name,
    price,
    NTILE(4) OVER (ORDER BY price) AS quartile
    FROM products;
 
 # トップ3を取得 (サブクエリまたはCTEと組み合わせ)
 WITH ranked_products AS (
    SELECT
       product_name,
       price,
       RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank_num
       FROM products
 )
 SELECT * FROM ranked_products WHERE rank_num <= 3;


集約ウィンドウ関数

集約関数に OVER 句を付けることで、ウィンドウ関数として使用できる。

 # 累積合計を計算
 SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS cumulative_sum
    FROM orders;
 
 # 移動平均を計算 (直近3行)
 SELECT
    order_date,
    amount,
    AVG(amount) OVER (
       ORDER BY order_date
       ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
    FROM orders;
 
 # グループごとの合計と全体に対する割合
 SELECT
    category_id,
    product_name,
    price,
    SUM(price) OVER (PARTITION BY category_id) AS category_total,
    price / SUM(price) OVER (PARTITION BY category_id) * 100 AS percentage
    FROM products;
 
 # 値との差分を計算
 SELECT
    product_name,
    price,
    LAG(price) OVER (ORDER BY price) AS prev_price,
    price - LAG(price) OVER (ORDER BY price) AS price_diff
    FROM products;
 
 # 最初の値と最後の値を取得
 SELECT
    order_date,
    amount,
    FIRST_VALUE(amount) OVER (ORDER BY order_date) AS first_amount,
    LAST_VALUE(amount) OVER (
       ORDER BY order_date
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_amount
    FROM orders;


ウィンドウフレーム句の種類を下表に示す。

ウィンドウフレーム句
フレーム句 説明
ROWS BETWEEN ... AND ... 行数でフレームを指定
RANGE BETWEEN ... AND ... 値の範囲でフレームを指定
UNBOUNDED PRECEDING パーティションの最初
n PRECEDING n行前
CURRENT ROW 現在行
n FOLLOWING n行後
UNBOUNDED FOLLOWING パーティションの最後



SELECT INTO

変数への代入

SELECT ... INTO を使用して、クエリ結果を変数に代入できる。

 # 単一の値を変数に代入
 SELECT COUNT(*) INTO @user_count FROM users;
 SELECT @user_count;
 
 # 複数の値を複数の変数に代入
 SELECT MIN(price), MAX(price) INTO @min_price, @max_price FROM products;
 SELECT @min_price, @max_price;
 
 # 特定の行の値を取得
 SELECT name, email INTO @user_name, @user_email
    FROM users
    WHERE id = 100;
 
 # ストアドプロシージャ内での使用
 DELIMITER //
 CREATE PROCEDURE get_user_count()
 BEGIN
    DECLARE user_count INT;
    SELECT COUNT(*) INTO user_count FROM users;
    SELECT user_count;
 END //
 DELIMITER ;


※注意
SELECT INTO は、結果が1行のみの場合に使用する。
複数行が返される場合はエラーになる。

ファイルへの出力

SELECT ... INTO OUTFILE を使用して、クエリ結果をファイルに出力できる。

 # 基本的なファイル出力
 SELECT * FROM users
    INTO OUTFILE '/tmp/users.txt';
 
 # CSV形式で出力
 SELECT * FROM users
    INTO OUTFILE '/tmp/users.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
 
 # TSV形式で出力
 SELECT * FROM users
    INTO OUTFILE '/tmp/users.tsv'
    FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\n';
 
 # ヘッダ行を含める
 SELECT 'id', 'name', 'email'
 UNION ALL
 SELECT id, name, email FROM users
    INTO OUTFILE '/tmp/users_with_header.csv'
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n';
 
 # バイナリデータの出力
 SELECT binary_data FROM files WHERE id = 1
    INTO DUMPFILE '/tmp/file.bin';


※注意
ファイル出力には、FILE権限が必要である。
secure_file_priv変数で指定されたディレクトリにのみ出力可能である。
同名のファイルが既に存在する場合はエラーになる。


パフォーマンス最適化

EXPLAIN文の活用

EXPLAIN 文を使用して、クエリの実行計画を確認できる。

 # 基本的なEXPLAIN
 EXPLAIN SELECT * FROM users WHERE id = 100;
 
 # EXPLAIN EXTENDED (追加情報を表示、MySQL 5.7以降は不要)
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 
 # EXPLAIN FORMAT=JSON (JSON形式で詳細情報を表示)
 EXPLAIN FORMAT=JSON
 SELECT u.name, o.order_date
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active';
 
 # EXPLAIN FORMAT=TREE (ツリー形式で表示、MySQL 8.0.16以降)
 EXPLAIN FORMAT=TREE
 SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;
 
 # EXPLAIN ANALYZE (実際に実行して統計情報を表示、MySQL 8.0.18以降)
 EXPLAIN ANALYZE
 SELECT * FROM users WHERE status = 'active';


EXPLAINの主要な出力項目を下表に示す。

EXPLAIN出力の主要項目
項目 説明
id SELECTの識別子
select_type SELECTのタイプ (SIMPLE, PRIMARY, SUBQUERY等)
table 参照しているテーブル
type JOINのタイプ (system, const, ref, range, index, ALL等)
possible_keys 使用可能なインデックス
key 実際に使用されたインデックス
key_len 使用されたインデックスのバイト長
ref インデックスと比較される値
rows 検査される行数の推定値
filtered フィルタリングされる行の割合
Extra 追加情報 (Using where, Using index等)


インデックスの活用

インデックスを適切に使用することで、クエリのパフォーマンスを大幅に向上できる。

 # WHERE句で使用するカラムにインデックスを作成
 CREATE INDEX idx_status ON users(status);
 
 # 複合インデックスの作成
 CREATE INDEX idx_status_created ON users(status, created_at);
 
 # カバリングインデックス (取得するカラムを全て含む)
 CREATE INDEX idx_covering ON users(status, name, email);
 
 # SHOW INDEXでインデックスを確認
 SHOW INDEX FROM users;
 
 # インデックスが使用されているか確認
 EXPLAIN SELECT * FROM users WHERE status = 'active';


インデックスを効果的に使用するための注意点を以下に示す。

  • WHERE句で使用するカラムにインデックスを作成する
    頻繁に検索条件として使用されるカラムにインデックスを作成する。
  • 複合インデックスの順序に注意する
    選択性の高いカラム (値の種類が多いカラム) を先頭に配置する。
  • インデックスのカーディナリティを考慮する
    カーディナリティが低い (値の種類が少ない) カラムはインデックスの効果が薄い。
  • 関数やカラムの計算はインデックスを使用できない
    WHERE YEAR(created_at) = 2024 ではなく、WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31' を使用する。
  • 否定条件 (!=, NOT IN) はインデックスを使用しにくい
    可能な限り肯定条件に書き換える。


クエリの最適化のヒント

クエリのパフォーマンスを向上させるための技法を以下に示す。

 # SELECT * を避け、必要なカラムのみを指定
 
 # 良い例
 SELECT id, name, email FROM users;
 
 # 悪い例
 SELECT * FROM users;
 
 # サブクエリの代わりにJOINを使用 (場合による)
 # サブクエリ
 SELECT * FROM users
    WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');
 
 # JOINに書き換え (通常は高速)
 SELECT DISTINCT u.*
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE o.status = 'completed';
 
 # EXISTSの使用 (大量データの場合はINより高速)
 SELECT * FROM users u
    WHERE EXISTS (
       SELECT 1 FROM orders o
          WHERE o.user_id = u.id AND o.status = 'completed'
    );
 
 # LIMITを使用して取得行数を制限
 SELECT * FROM logs ORDER BY created_at DESC LIMIT 1000;
 
 # オプティマイザーヒントの使用 (MySQL 8.0以降)
 SELECT /*+ INDEX(users idx_status) */ * FROM users WHERE status = 'active';
 
 # 結合順序のヒント
 SELECT /*+ JOIN_ORDER(u, o) */ u.name, o.order_date
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id;
 
 # インデックスマージのヒント
 SELECT /*+ INDEX_MERGE(users idx_status, idx_role) */ *
    FROM users
    WHERE status = 'active' OR role = 'admin';


パフォーマンス最適化のベストプラクティスを以下に示す。

  • テーブル統計を最新に保つ
    ANALYZE TABLE文を定期的に実行する。
  • クエリキャッシュの活用
    同じクエリが頻繁に実行される場合に有効 (MySQL 8.0では削除)。
  • スロークエリログを分析する
    slow_query_logを有効にして、遅いクエリを特定する。
  • 適切なストレージエンジンを選択する
    トランザクションが必要な場合はInnoDB、読み取り専用の場合はMyISAMも選択肢。
  • パーティショニングの検討
    大量データを扱う場合、テーブルパーティショニングを検討する。



「http://mochiuwiki.e2.valueserver.jp/index.php?title=MySQL_-_SELECT&oldid=14262」から取得