MySQL - UNION - 版の履歴

Wiki: ページの作成:「== 概要 == UNION演算子は、2つ以上のSELECT文の結果を結合して、単一の結果セットを返すセット演算子である。

`UNION` はデフォルトで重複行を削除し、`UNION ALL` は重複行を含めて全ての行を返す。
複数のテーブルや条件から取得したデータを1つの結果セットにまとめる際に有用である。

MySQL 8.0.31以降では、INTERSECT 2026-02-08T03:55:03Z ページの作成:「== 概要 == UNION演算子は、2つ以上のSELECT文の結果を結合して、単一の結果セットを返すセット演算子である。<br> <br> <code>UNION</code> はデフォルトで重複行を削除し、<code>UNION ALL</code> は重複行を含めて全ての行を返す。<br> 複数のテーブルや条件から取得したデータを1つの結果セットにまとめる際に有用である。<br> <br> MySQL 8.0.31以降では、<code>INTERSECT</…」新規ページ == 概要 == UNION演算子は、2つ以上のSELECT文の結果を結合して、単一の結果セットを返すセット演算子である。<br> <br> <code>UNION</code> はデフォルトで重複行を削除し、<code>UNION ALL</code> は重複行を含めて全ての行を返す。<br> 複数のテーブルや条件から取得したデータを1つの結果セットにまとめる際に有用である。<br> <br> MySQL 8.0.31以降では、<code>INTERSECT</code> (交差) と <code>EXCEPT</code> (差集合) 演算子も使用可能になり、セット演算の機能が大幅に拡張された。<br> これらの演算子は、SQL標準に準拠した集合演算を提供する。<br> <br> UNIONを使用する時は、各SELECT文の列数を一致させる必要がある。<br> 列名は最初のSELECT文のものが使用され、データ型は自動的に互換性のある型に変換される。<br> <br> パフォーマンスの観点では、<code>UNION ALL</code> は重複排除の処理が不要なため、<code>UNION</code> より高速である。<br> 大規模なデータセットでは、<code>UNION ALL</code> の方が2〜3倍高速になることもある。<br> <br> <code>ORDER BY</code> と <code>LIMIT</code> を組み合わせることにより、結果セット全体のソートや行数の制限が可能である。<br> 個別の <code>SELECT</code> 文にも <code>ORDER BY</code> と <code>LIMIT</code> を適用できるが、その場合は括弧で囲む必要がある。<br> <br><br> == UNION演算子 == ==== 基本構文 ==== UNIONは、2つ以上のSELECT文の結果を結合する。<br> <br> <syntaxhighlight lang="mysql"> # 基本構文 SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2; # UNION ALL (重複を含める) SELECT column1, column2 FROM table1 UNION ALL SELECT column1, column2 FROM table2; # 3つ以上のSELECT文を結合 SELECT column1 FROM table1 UNION SELECT column1 FROM table2 UNION SELECT column1 FROM table3; </syntaxhighlight> <br> UNIONの基本的なルールを以下に示す。<br> <br> <center> {| class="wikitable" |+ UNIONの注意点 ! 項目 !! 説明 |- | 列数の一致 || 各 <code>SELECT</code> 文の列数は一致する必要がある。 |- | 列名 || 結果セットの列名は、最初の <code>SELECT</code> 文のものが使用される。 |- | データ型 || 各列のデータ型は互換性がある必要がある。型強制ルールに従って自動変換される。 |- | 重複の扱い || <code>UNION</code> は重複行を削除し、<code>UNION ALL</code> は重複を保持する。 |} </center> <br> <syntaxhighlight lang="mysql"> # 複数テーブルから従業員名を取得 SELECT name FROM employees UNION SELECT name FROM contractors; # 重複を含めて取得 SELECT name FROM employees UNION ALL SELECT name FROM contractors; # 異なる条件で同じテーブルから取得 SELECT product_name, price FROM products WHERE category_id = 1 UNION SELECT product_name, price FROM products WHERE category_id = 2; </syntaxhighlight> <br> ==== UNION vs UNION ALL ==== <code>UNION</code> と <code>UNION ALL</code> の違いは、重複行の扱いである。<br> <br> <center> {| class="wikitable" |+ UNIONとUNION ALLの比較 ! 項目 !! UNION !! UNION ALL |- | 重複行の扱い || 重複行を削除 (DISTINCT) || 重複行を含めて全ての行を返す |- | 一時テーブル || インデックス付き一時テーブルを作成 || 一時テーブル不要 |- | パフォーマンス || 重複排除のため遅い || 高速 (大規模データで2-3倍) |- | 使用場面 || 重複を排除したい場合 || 全ての行が必要な場合 |} </center> <br> <syntaxhighlight lang="mysql"> # UNION (重複削除) SELECT 'A' AS col UNION SELECT 'A' AS col; # 結果: 1行 ('A') # UNION ALL (重複保持) SELECT 'A' AS col UNION ALL SELECT 'A' AS col; # 結果: 2行 ('A', 'A') # パフォーマンス比較例 # 遅い (重複排除処理が必要) SELECT user_id FROM orders WHERE status = 'completed' UNION SELECT user_id FROM orders WHERE status = 'shipped'; # 高速 (重複排除なし) SELECT user_id FROM orders WHERE status = 'completed' UNION ALL SELECT user_id FROM orders WHERE status = 'shipped'; </syntaxhighlight> <br> 重複が存在しないことが明らかな場合は、<code>UNION ALL</code> を使用することでパフォーマンスを向上できる。<br> <br> <syntaxhighlight lang="mysql"> # 異なるテーブルからの結合 (重複なし) SELECT id, 'employee' AS type FROM employees UNION ALL SELECT id, 'contractor' AS type FROM contractors; # 異なる条件での結合 (重複なし) SELECT product_id FROM orders WHERE created_at < '2024-01-01' UNION ALL SELECT product_id FROM orders WHERE created_at >= '2024-01-01'; </syntaxhighlight> <br> ==== 列数とデータ型 ==== UNIONを使用する時は、各SELECT文の列数を一致させる必要がある。<br> <br> 列数が一致しない場合は、エラーが発生する。<br> <br> <syntaxhighlight lang="mysql"> # エラーになる例 (列数が一致しない) SELECT name, age FROM users UNION SELECT name FROM employees; # ERROR 1222 (21000): The used SELECT statements have a different number of columns # 正しい形式 (列数を一致させる) SELECT name, age FROM users UNION SELECT name, NULL AS age FROM employees; </syntaxhighlight> <br> データ型は、互換性のある型である必要がある。<br> 型が異なる場合は、MySQLの型強制ルールに従って自動的に変換される。<br> <br> <syntaxhighlight lang="mysql"> # 数値型と文字列型の結合 (文字列型に変換される) SELECT id FROM users UNION SELECT 'admin' AS id; # 異なる数値型の結合 (精度の高い型に変換される) SELECT price FROM products # DECIMAL型 UNION SELECT discount FROM coupons; # FLOAT型 # 結果: DOUBLE型 # 日付型と文字列型の結合 SELECT created_at FROM orders # DATETIME型 UNION SELECT '2024-01-01' AS created_at; # 文字列 # 結果: 文字列型 </syntaxhighlight> <br> 列名は、最初のSELECT文のものが使用される。<br> <br> <syntaxhighlight lang="mysql"> # 列名の例 SELECT name AS employee_name FROM employees UNION SELECT name AS contractor_name FROM contractors; # 結果の列名: employee_name # 列名を明示的に指定 SELECT name FROM employees UNION SELECT full_name FROM contractors; # 結果の列名: name </syntaxhighlight> <br><br> == INTERSECT演算子 (MySQL 8.0.31以降) == ==== 基本構文 ==== <code>INTERSECT</code> は、2つ以上のSELECT文の結果の共通行のみを返す。(交差)<br> <br> MySQL 8.0.31以降で使用可能である。<br> <br> <syntaxhighlight lang="mysql"> # 基本構文 SELECT column1, column2 FROM table1 INTERSECT SELECT column1, column2 FROM table2; # INTERSECT DISTINCT (デフォルト、重複削除) SELECT column1 FROM table1 INTERSECT DISTINCT SELECT column1 FROM table2; # INTERSECT ALL (重複保持) SELECT column1 FROM table1 INTERSECT ALL SELECT column1 FROM table2; # 3つ以上のSELECT文の交差 SELECT column1 FROM table1 INTERSECT SELECT column1 FROM table2 INTERSECT SELECT column1 FROM table3; </syntaxhighlight> <br> <code>INTERSECT</code> は、両方の結果セットに存在する行のみを返す。<br> <br> <syntaxhighlight lang="mysql"> # 両方の注文に含まれる商品を取得 SELECT product_id FROM orders WHERE user_id = 1 INTERSECT SELECT product_id FROM orders WHERE user_id = 2; # 従業員と管理者の両方に含まれるユーザ SELECT user_id FROM employees INTERSECT SELECT user_id FROM managers; # 両方のカテゴリに属する商品 SELECT product_id FROM product_categories WHERE category_id = 1 INTERSECT SELECT product_id FROM product_categories WHERE category_id = 2; </syntaxhighlight> <br> ==== INTERSECT DISTINCTとINTERSECT ALL ==== <code>INTERSECT DISTINCT</code> (デフォルト) は重複を削除し、<code>INTERSECT ALL</code> は重複を保持する。<br> <br> <syntaxhighlight lang="mysql"> # INTERSECT DISTINCT (重複削除) SELECT value FROM table1 # 'A', 'A', 'B' INTERSECT DISTINCT SELECT value FROM table2; # 'A', 'A', 'C' # 結果: 'A' (1行) # INTERSECT ALL (重複保持) SELECT value FROM table1 # 'A', 'A', 'B' INTERSECT ALL SELECT value FROM table2; # 'A', 'A', 'C' # 結果: 'A', 'A' (2行) </syntaxhighlight> <br> <code>INTERSECT ALL</code> では、各値の出現回数の最小値が結果に含まれる。<br> <br> <syntaxhighlight lang="mysql"> # 出現回数の例 SELECT value FROM table1 # 'A', 'A', 'A', 'B' INTERSECT ALL SELECT value FROM table2; # 'A', 'A', 'C' # 結果: 'A', 'A' (table1に3回、table2に2回 → 最小値2回) </syntaxhighlight> <br> ==== 可換性と演算子優先順位 ==== <code>INTERSECT</code> は可換性がある。(順序を入れ替えても結果は同じ)<br> <br> <syntaxhighlight lang="mysql"> # これらは同じ結果 SELECT col FROM table1 INTERSECT SELECT col FROM table2; SELECT col FROM table2 INTERSECT SELECT col FROM table1; </syntaxhighlight> <br> セット演算子の優先順位は以下の通りである。<br> <br> <center> {| class="wikitable" |+ セット演算子の優先順位 ! 優先順位 !! 演算子 |- | 高 || <code>INTERSECT</code> |- | 低 || <code>UNION</code>, <code>EXCEPT</code> |} </center> <br> <syntaxhighlight lang="mysql"> # INTERSECTが先に評価される SELECT col FROM table1 UNION SELECT col FROM table2 INTERSECT SELECT col FROM table3; # (table2 INTERSECT table3) が先に評価され、その後 table1 と UNION される # 括弧で優先順位を明示 (SELECT col FROM table1 UNION SELECT col FROM table2) INTERSECT SELECT col FROM table3; </syntaxhighlight> <br> ==== MySQL 8.0.31以前の代替方法 ==== MySQL 8.0.31以前では、<code>INNER JOIN</code> または <code>EXISTS</code> を使用して INTERSECTを代替できる。<br> <br> <syntaxhighlight lang="mysql"> # INTERSECTの代替 (INNER JOIN) SELECT DISTINCT t1.col FROM table1 t1 INNER JOIN table2 t2 ON t1.col = t2.col; # INTERSECTの代替 (EXISTS) SELECT DISTINCT col FROM table1 t1 WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t2.col = t1.col); # INTERSECT ALLの代替 (より複雑) SELECT t1.col FROM table1 t1 INNER JOIN ( SELECT col, COUNT() AS cnt FROM table2 GROUP BY col ) t2 ON t1.col = t2.col WHERE ( SELECT COUNT() FROM table1 t1_inner WHERE t1_inner.col = t1.col AND t1_inner.rowid <= t1.rowid ) <= t2.cnt; </syntaxhighlight> <br><br> == EXCEPT演算子 (MySQL 8.0.31以降) == ==== 基本構文 ==== <code>EXCEPT</code> は、第1のSELECT結果から第2のSELECT結果を除外する。(差集合)<br> <br> MySQL 8.0.31以降で使用可能である。<br> <br> <syntaxhighlight lang="mysql"> # 基本構文 SELECT column1, column2 FROM table1 EXCEPT SELECT column1, column2 FROM table2; # EXCEPT DISTINCT (デフォルト、重複削除) SELECT column1 FROM table1 EXCEPT DISTINCT SELECT column1 FROM table2; # EXCEPT ALL (重複保持) SELECT column1 FROM table1 EXCEPT ALL SELECT column1 FROM table2; # 複数のEXCEPT SELECT column1 FROM table1 EXCEPT SELECT column1 FROM table2 EXCEPT SELECT column1 FROM table3; </syntaxhighlight> <br> <code>EXCEPT</code> は、第1の結果セットから第2の結果セットに含まれる行を除外する。<br> <br> <syntaxhighlight lang="mysql"> # ユーザ1が注文した商品のうち、ユーザ2が注文していない商品 SELECT product_id FROM orders WHERE user_id = 1 EXCEPT SELECT product_id FROM orders WHERE user_id = 2; # 全従業員から管理者を除外 SELECT user_id FROM employees EXCEPT SELECT user_id FROM managers; # 全商品から注文済み商品を除外 (在庫のみ) SELECT product_id FROM products EXCEPT SELECT product_id FROM order_items; </syntaxhighlight> <br> ==== EXCEPT DISTINCTとEXCEPT ALL ==== <code>EXCEPT DISTINCT</code> (デフォルト) は重複を削除し、<code>EXCEPT ALL</code> は重複を保持する。<br> <br> <syntaxhighlight lang="mysql"> # EXCEPT DISTINCT (重複削除) SELECT value FROM table1 # 'A', 'A', 'B', 'C' EXCEPT DISTINCT SELECT value FROM table2; # 'A', 'D' # 結果: 'B', 'C' (重複削除) # EXCEPT ALL (重複保持) SELECT value FROM table1 # 'A', 'A', 'B', 'C' EXCEPT ALL SELECT value FROM table2; # 'A', 'D' # 結果: 'A', 'B', 'C' (table1の2つの'A'から、table2の1つの'A'を除外 → 1つの'A'が残る) </syntaxhighlight> <br> <code>EXCEPT ALL</code> では、table1の出現回数からtable2の出現回数を引いた回数が結果に含まれる。<br> <br> <syntaxhighlight lang="mysql"> # 出現回数の例 SELECT value FROM table1 # 'A', 'A', 'A', 'B' EXCEPT ALL SELECT value FROM table2; # 'A', 'A', 'C' # 結果: 'A', 'B' (table1の3つの'A'から、table2の2つの'A'を除外 → 1つの'A'が残る) </syntaxhighlight> <br> ==== 非可換性 ==== EXCEPTは非可換である。(順序を入れ替えると結果が異なる)<br> <br> <syntaxhighlight lang="mysql"> # A EXCEPT B SELECT col FROM table1 EXCEPT SELECT col FROM table2; # table1にあり、table2にない行 # B EXCEPT A SELECT col FROM table2 EXCEPT SELECT col FROM table1; # table2にあり、table1にない行 # これらは異なる結果 </syntaxhighlight> <br> ==== MySQL 8.0.31以前の代替方法 ==== MySQL 8.0.31以前では、<code>LEFT JOIN WHERE IS NULL</code> または <code>NOT EXISTS</code> を使用して、EXCEPTを代替できる。<br> <br> <syntaxhighlight lang="mysql"> # EXCEPTの代替 (LEFT JOIN WHERE IS NULL) SELECT DISTINCT t1.col FROM table1 t1 LEFT JOIN table2 t2 ON t1.col = t2.col WHERE t2.col IS NULL; # EXCEPTの代替 (NOT EXISTS) SELECT DISTINCT col FROM table1 t1 WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2.col = t1.col); # EXCEPT ALLの代替 (より複雑) SELECT t1.col FROM table1 t1 LEFT JOIN ( SELECT col, COUNT() AS cnt FROM table2 GROUP BY col ) t2 ON t1.col = t2.col WHERE ( SELECT COUNT() FROM table1 t1_inner WHERE t1_inner.col = t1.col AND t1_inner.rowid <= t1.rowid ) > COALESCE(t2.cnt, 0); </syntaxhighlight> <br><br> == ORDER BYとLIMITの組み合わせ == ==== 全体に対するORDER BYとLIMIT ==== <code>UNION</code> の結果全体をソートしたり、行数を制限したりする場合は、最後のセット演算子の後に <code>ORDER BY</code> や <code>LIMIT</code> を指定する。<br> <br> <syntaxhighlight lang="mysql"> # 全体をソート SELECT name, age FROM employees UNION SELECT name, age FROM contractors ORDER BY age DESC; # 全体を制限 SELECT product_id FROM orders WHERE user_id = 1 UNION SELECT product_id FROM orders WHERE user_id = 2 LIMIT 10; # ソートと制限の組み合わせ SELECT name, salary FROM employees WHERE department_id = 1 UNION SELECT name, salary FROM employees WHERE department_id = 2 ORDER BY salary DESC LIMIT 5; </syntaxhighlight> <br> ORDER BYで指定する列名は、最初のSELECT文の列名を使用する。<br> <br> <syntaxhighlight lang="mysql"> # 列名の指定 SELECT name AS employee_name, salary FROM employees UNION SELECT full_name AS contractor_name, rate FROM contractors ORDER BY employee_name; # 最初のSELECTの列名を使用 # エイリアスなしの場合 SELECT name, salary FROM employees UNION SELECT name, rate FROM contractors ORDER BY name, salary; # 列番号での指定も可能 SELECT name, salary FROM employees UNION SELECT name, rate FROM contractors ORDER BY 2 DESC, 1; # 2列目 (salary/rate) 降順、1列目 (name) 昇順 </syntaxhighlight> <br> ==== 個別のSELECT文に対するORDER BYとLIMIT ==== 個別のSELECT文に <code>ORDER BY</code> や <code>LIMIT</code> を適用する場合は、丸括弧 <code>()</code> で囲む必要がある。<br> <br> <syntaxhighlight lang="mysql"> # 個別のSELECT文に対してLIMITを適用 (SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5) UNION (SELECT name, salary FROM contractors ORDER BY salary DESC LIMIT 5); # 各テーブルから上位5件を取得し、結合 # 個別のSELECT文に対してORDER BYとLIMITを適用 (SELECT product_name, price FROM products WHERE category_id = 1 ORDER BY price DESC LIMIT 3) UNION ALL (SELECT product_name, price FROM products WHERE category_id = 2 ORDER BY price DESC LIMIT 3); # 条件付きで上位を取得 (SELECT user_id, SUM(amount) AS total FROM orders WHERE status = 'completed' GROUP BY user_id ORDER BY total DESC LIMIT 10) UNION (SELECT user_id, SUM(amount) AS total FROM orders WHERE status = 'pending' GROUP BY user_id ORDER BY total DESC LIMIT 10); </syntaxhighlight> <br> 丸括弧 <code>()</code> で囲まない場合、<code>ORDER BY</code> と <code>LIMIT</code> は全体に適用される。<br> <br> <syntaxhighlight lang="mysql"> # 括弧なし (全体に適用) SELECT name FROM employees UNION SELECT name FROM contractors ORDER BY name LIMIT 10; # UNION の結果全体を name でソートし、10件に制限 # 括弧あり (個別に適用) (SELECT name FROM employees ORDER BY name LIMIT 10) UNION (SELECT name FROM contractors ORDER BY name LIMIT 10); # 各SELECTで10件ずつ取得し、結合 (最大20件) </syntaxhighlight> <br> ==== 注意事項 ==== 括弧内と外の両方に <code>ORDER BY</code> や <code>LIMIT</code> がある場合、結果が未定義になる可能性がある。<br> <br> <syntaxhighlight lang="mysql"> # 未定義の動作 (推奨しない) (SELECT name FROM employees ORDER BY name LIMIT 10) UNION (SELECT name FROM contractors ORDER BY name LIMIT 10) ORDER BY name LIMIT 5; # 各SELECTのORDER BYは無視される可能性がある </syntaxhighlight> <br> 個別のSELECT文において、ORDER BYを使用する場合は、<code>LIMIT</code> と組み合わせる必要がある。<br> <code>LIMIT</code> がない場合、ORDER BYは無視される可能性がある。<br> <br> <syntaxhighlight lang="mysql"> # ORDER BYが無視される可能性がある (SELECT name FROM employees ORDER BY name) UNION (SELECT name FROM contractors); # LIMITと組み合わせると有効 (SELECT name FROM employees ORDER BY name LIMIT 100) UNION (SELECT name FROM contractors); </syntaxhighlight> <br><br> == サンプルクエリ == ==== 複数テーブルのデータ統合 ==== 異なるテーブルから同じ形式のデータを取得して統合する。<br> <br> <syntaxhighlight lang="mysql"> # 従業員と契約社員のリストを統合 SELECT id, name, 'employee' AS type, hire_date AS start_date FROM employees UNION ALL SELECT id, name, 'contractor' AS type, contract_start FROM contractors ORDER BY start_date DESC; # 複数の注文テーブルを統合 SELECT order_id, user_id, amount, created_at FROM orders_2023 UNION ALL SELECT order_id, user_id, amount, created_at FROM orders_2024 ORDER BY created_at DESC; # ログテーブルの統合 SELECT log_id, message, 'info' AS level, created_at FROM info_logs UNION ALL SELECT log_id, message, 'error' AS level, created_at FROM error_logs UNION ALL SELECT log_id, message, 'warning' AS level, created_at FROM warning_logs ORDER BY created_at DESC; </syntaxhighlight> <br> ==== FULL OUTER JOINの代替 ==== MySQLにはFULL OUTER JOINが存在しないため、<code>LEFT JOIN</code> と <code>RIGHT JOIN</code> を <code>UNION</code> で結合することで代替できる。<br> <br> <syntaxhighlight lang="mysql"> # FULL OUTER JOINの代替 SELECT u.user_id, u.name, o.order_id FROM users u LEFT JOIN orders o ON u.user_id = o.user_id UNION SELECT u.user_id, u.name, o.order_id FROM users u RIGHT JOIN orders o ON u.user_id = o.user_id; # FULL OUTER JOINの代替 (重複削除なし) SELECT e.employee_id, e.name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id UNION ALL SELECT e.employee_id, e.name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.id WHERE e.employee_id IS NULL; </syntaxhighlight> <br> ==== 条件付き統合 ==== 異なる条件でデータを取得し、優先度ラベルを付与して統合する。<br> <br> <syntaxhighlight lang="mysql"> # 優先度ラベル付与 SELECT task_id, title, 'high' AS priority FROM tasks WHERE priority_score > 80 UNION ALL SELECT task_id, title, 'medium' AS priority FROM tasks WHERE priority_score BETWEEN 50 AND 80 UNION ALL SELECT task_id, title, 'low' AS priority FROM tasks WHERE priority_score < 50 ORDER BY priority_score DESC; # ステータス別の集計 SELECT 'completed' AS status, COUNT() AS count FROM orders WHERE status = 'completed' UNION ALL SELECT 'pending' AS status, COUNT() AS count FROM orders WHERE status = 'pending' UNION ALL SELECT 'cancelled' AS status, COUNT() AS count FROM orders WHERE status = 'cancelled'; # 期間別のデータ統合 SELECT product_id, SUM(amount) AS total, 'Q1' AS quarter FROM sales WHERE QUARTER(sale_date) = 1 GROUP BY product_id UNION ALL SELECT product_id, SUM(amount) AS total, 'Q2' AS quarter FROM sales WHERE QUARTER(sale_date) = 2 GROUP BY product_id UNION ALL SELECT product_id, SUM(amount) AS total, 'Q3' AS quarter FROM sales WHERE QUARTER(sale_date) = 3 GROUP BY product_id UNION ALL SELECT product_id, SUM(amount) AS total, 'Q4' AS quarter FROM sales WHERE QUARTER(sale_date) = 4 GROUP BY product_id ORDER BY product_id, quarter; </syntaxhighlight> <br> ==== 複数のセット演算の組み合わせ ==== <code>UNION</code>、<code>INTERSECT</code>、<code>EXCEPT</code> を組み合わせて複雑な集合演算を行う。<br> <br> <syntaxhighlight lang="mysql"> # ユーザ1または2が注文したが、ユーザ3は注文していない商品 (SELECT product_id FROM orders WHERE user_id = 1 UNION SELECT product_id FROM orders WHERE user_id = 2) EXCEPT SELECT product_id FROM orders WHERE user_id = 3; # カテゴリ1と2の両方に属し、かつ在庫がある商品 SELECT product_id FROM product_categories WHERE category_id = 1 INTERSECT SELECT product_id FROM product_categories WHERE category_id = 2 INTERSECT SELECT product_id FROM inventory WHERE stock > 0; # 全従業員から、管理者または退職者を除外 SELECT user_id FROM employees EXCEPT (SELECT user_id FROM managers UNION SELECT user_id FROM resigned_employees); </syntaxhighlight> <br><br> == パフォーマンス最適化 == ==== UNIONとUNION ALLのパフォーマンス ==== <code>UNION</code> は、重複排除のために内部一時テーブル (インデックス付き) を作成する。<br> <code>UNION ALL</code> は、一時テーブルを作成せず、全ての行をそのまま返すため高速である。<br> <br> 大規模なデータセットでは、<code>UNION ALL</code> の方が2-3倍高速になることがある。<br> <br> <syntaxhighlight lang="mysql"> # 遅い (一時テーブルを作成して重複排除) SELECT user_id FROM orders WHERE status = 'completed' UNION SELECT user_id FROM orders WHERE status = 'shipped'; # 高速 (一時テーブル不要) SELECT user_id FROM orders WHERE status = 'completed' UNION ALL SELECT user_id FROM orders WHERE status = 'shipped'; </syntaxhighlight> <br> 重複が存在しないことが明らかな場合は、<code>UNION ALL</code> を使用する。<br> <br> <syntaxhighlight lang="mysql"> # 異なるテーブル (重複なし) SELECT id FROM employees UNION ALL SELECT id FROM contractors; # 排他的な条件 (重複なし) SELECT product_id FROM products WHERE category_id = 1 UNION ALL SELECT product_id FROM products WHERE category_id = 2; </syntaxhighlight> <br> ==== 条件の早期適用 ==== WHERE句を各SELECT内に配置することにより、不要な行を早期に除外できる。<br> <br> <syntaxhighlight lang="mysql"> # 効率的 (各SELECTで条件を適用) SELECT name FROM employees WHERE department_id = 1 UNION SELECT name FROM employees WHERE department_id = 2; # 非効率 (UNION後にフィルタリング) SELECT name, department_id FROM ( SELECT name, department_id FROM employees UNION SELECT name, department_id FROM contractors ) AS combined WHERE department_id IN (1, 2); </syntaxhighlight> <br> ==== LIMITの最適化 ==== 個別のSELECT文にLIMITを適用することにより、取得行数を制限できる。<br> <br> <syntaxhighlight lang="mysql"> # 効率的 (各SELECTで上位のみ取得) (SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 10) UNION ALL (SELECT name, salary FROM contractors ORDER BY salary DESC LIMIT 10) ORDER BY salary DESC LIMIT 10; # 非効率 (全行を取得してから制限) SELECT name, salary FROM employees UNION ALL SELECT name, salary FROM contractors ORDER BY salary DESC LIMIT 10; </syntaxhighlight> <br> ==== FOR UPDATEとLOCK IN SHARE MODEの注意事項 ==== <code>FOR UPDATE</code> や <code>LOCK IN SHARE MODE</code> を使用する場合は、個別のSELECT文を括弧で囲む必要がある。<br> <br> <syntaxhighlight lang="mysql"> # 正しい形式 (括弧で囲む) (SELECT id, name FROM employees WHERE department_id = 1 FOR UPDATE) UNION (SELECT id, name FROM employees WHERE department_id = 2 FOR UPDATE); # エラーになる形式 (括弧なし) SELECT id, name FROM employees WHERE department_id = 1 UNION SELECT id, name FROM employees WHERE department_id = 2 FOR UPDATE; # ERROR: Incorrect usage of UNION and FOR UPDATE </syntaxhighlight> <br> ==== 一時テーブルの監視 ==== <code>UNION</code> が内部一時テーブルを作成しているかを確認するには、<code>SHOW STATUS</code> を使用する。<br> <br> <syntaxhighlight lang="mysql"> # 一時テーブルの作成状況を確認 SHOW STATUS LIKE 'Created_tmp%'; # Created_tmp_disk_tables: ディスク上に作成された一時テーブル数 # Created_tmp_tables: メモリ上に作成された一時テーブル数 # クエリ実行前にリセット FLUSH STATUS; # クエリ実行 SELECT name FROM employees UNION SELECT name FROM contractors; # 一時テーブルの作成数を確認 SHOW STATUS LIKE 'Created_tmp%'; </syntaxhighlight> <br> <code>Created_tmp_disk_tables</code> が増加している場合は、一時テーブルがディスクに作成されており、パフォーマンスに影響する可能性がある。<br> <br> <syntaxhighlight lang="mysql"> # 一時テーブルのサイズ制限を確認 SHOW VARIABLES LIKE 'tmp_table_size'; SHOW VARIABLES LIKE 'max_heap_table_size'; # 必要に応じて増やす (セッションレベル) SET SESSION tmp_table_size = 64 1024 * 1024; # 64MB SET SESSION max_heap_table_size = 64 * 1024 * 1024; # 64MB </syntaxhighlight> <br><br> {{#seo: |title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki |keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,電気回路,電子回路,基板,プリント基板 |description={{PAGENAME}} - 電子回路とSUSE Linuxに関する情報 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux |image=/resources/assets/MochiuLogo_Single_Blue.png }} FORCETOC [[カテゴリ:MySQL]]