【SQL】UNION完全ガイド｜UNION ALL・INTERSECT・EXCEPT/MINUSの違いと実務パターン

-- 顧客テーブルとリードテーブルを縦に統合
SELECT name, email, '顧客' AS source
FROM   customers
UNION
SELECT name, email, 'リード'
FROM   leads;

-- テーブルごとに列数が違う場合はNULLやリテラルで補う
SELECT product_id, product_name, price,    NULL AS category
FROM   products
UNION ALL
SELECT service_id,  service_name, monthly_fee, category
FROM   services;

-- MySQL での実行計画比較
EXPLAIN
SELECT id, name FROM table_a
UNION ALL
SELECT id, name FROM table_b;
-- Extra に "Using temporary; Using filesort" が出ない → ソートなし

EXPLAIN
SELECT id, name FROM table_a
UNION
SELECT id, name FROM table_b;
-- Extra に "Using temporary; Using filesort" が出ることがある

-- 顧客でもあり、かつニュースレター購読者でもある人を抽出
SELECT email FROM customers
INTERSECT
SELECT email FROM newsletter_subscribers;

-- INTERSECTが使えない旧バージョン向け
SELECT DISTINCT c.email
FROM   customers c
INNER JOIN newsletter_subscribers n ON c.email = n.email;

-- 顧客リストから「注文がある顧客」を差し引く
SELECT customer_id FROM customers
EXCEPT
SELECT DISTINCT customer_id FROM orders;

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

-- 東・西・北の売上テーブルを一つに統合
SELECT sale_date, amount, '東' AS region FROM sales_east
UNION ALL
SELECT sale_date, amount, '西'            FROM sales_west
UNION ALL
SELECT sale_date, amount, '北'            FROM sales_north;

-- NG: この書き方だと最後の UNION が全体に重複排除をかける
SELECT a FROM t1
UNION ALL
SELECT a FROM t2
UNION      -- ← ここで t1+t2 の結果と t3 を UNION（重複排除）
SELECT a FROM t3;

-- OK: サブクエリで先に UNION ALL をまとめてから UNION
SELECT a FROM (
    SELECT a FROM t1
    UNION ALL
    SELECT a FROM t2
) sub
UNION
SELECT a FROM t3;

-- UNION 全体の結果をソート → ORDER BY は末尾に1つだけ
SELECT name, '顧客' AS type FROM customers
UNION ALL
SELECT name, 'リード'       FROM leads
ORDER BY name;

-- 各SELECT内でソートしたい場合はサブクエリ化する
-- ※ MySQL は外側のORDER BYが上書きするため、サブクエリ内ORDERは保証されない
SELECT * FROM (
    SELECT name, created_at FROM customers ORDER BY created_at DESC LIMIT 10
) top_customers
UNION ALL
SELECT * FROM (
    SELECT name, created_at FROM leads    ORDER BY created_at DESC LIMIT 10
) top_leads;

-- 全体で上位N件だけ取得
SELECT name, sale_date FROM sales_east
UNION ALL
SELECT name, sale_date FROM sales_west
ORDER BY sale_date DESC
LIMIT 100;

SELECT NULL AS val
UNION
SELECT NULL;
-- 結果: 1行（NULLが1行になる）

SELECT NULL AS val
UNION ALL
SELECT NULL;
-- 結果: 2行（重複排除なし）

SELECT
    region,
    SUM(amount) AS total_amount,
    COUNT(*)    AS order_count
FROM (
    SELECT region, amount FROM sales_online
    UNION ALL
    SELECT region, amount FROM sales_offline
    UNION ALL
    SELECT region, amount FROM sales_partner
) all_sales
GROUP BY region
ORDER BY total_amount DESC;

SELECT product_id, 'Q1' AS quarter, q1_sales AS sales FROM quarterly_sales
UNION ALL
SELECT product_id, 'Q2',            q2_sales          FROM quarterly_sales
UNION ALL
SELECT product_id, 'Q3',            q3_sales          FROM quarterly_sales
UNION ALL
SELECT product_id, 'Q4',            q4_sales          FROM quarterly_sales
ORDER BY product_id, quarter;

SELECT
    category_id AS id,
    category_name AS name,
    NULL         AS parent_id,
    'category'   AS item_type
FROM categories
UNION ALL
SELECT
    product_id,
    product_name,
    category_id,
    'product'
FROM products
ORDER BY item_type, name;

-- title か description のどちらかにキーワードが含まれる記事を検索
SELECT article_id, title FROM articles WHERE title       LIKE '%SQL%'
UNION
SELECT article_id, title FROM articles WHERE description LIKE '%SQL%';
-- UNION で重複（両方に含まれる記事）を自動排除

-- 本番DB (prod_db) にあってステージング (stg_db) にない商品
SELECT product_id, sku FROM prod_db.products
EXCEPT
SELECT product_id, sku FROM stg_db.products;

-- NG: UNION後にWHEREをかけると最適化されない可能性
SELECT id, name FROM t1
UNION ALL
SELECT id, name FROM t2
WHERE name = '山田';  -- t2 にしか効かない

-- OK: 各SELECT内にWHEREを書く
SELECT id, name FROM t1 WHERE name = '山田'
UNION ALL
SELECT id, name FROM t2 WHERE name = '山田';

-- NG: 列数が違う
SELECT id, name, email FROM customers
UNION ALL
SELECT id, name          FROM leads;  -- email がない → エラー

-- OK: NULL で補完
SELECT id, name, email FROM customers
UNION ALL
SELECT id, name, NULL  FROM leads;

-- INT と VARCHAR が混在すると VARCHAR に統一される
SELECT 1   AS val
UNION ALL
SELECT '1A';
-- 結果: '1'（数値1が文字列に変換）と '1A' の2行
-- 集計関数を使うと予期しない結果になる場合がある

-- UNIONのORDER BYでは1つ目のSELECTの列名を使う
SELECT name, '顧客' AS type FROM customers
UNION ALL
SELECT name, 'リード'       FROM leads
ORDER BY name;          -- OK: 1つ目のSELECTの列名
-- ORDER BY type;       -- OK: エイリアスが使える（RDBMS依存）
-- ORDER BY 2;          -- OK: 列番号指定（type = 2列目）

-- UNION ALL の件数と UNION の件数を比較
SELECT
    (SELECT COUNT(*) FROM (
        SELECT email FROM customers UNION ALL SELECT email FROM leads
    ) t1) AS union_all_count,
    (SELECT COUNT(*) FROM (
        SELECT email FROM customers UNION     SELECT email FROM leads
    ) t2) AS union_count;
-- union_all_count > union_count なら重複あり

-- 特定列の重複を排除するには DISTINCT も使える
-- UNION（重複排除）と SELECT DISTINCT は結果が同じになることが多い

-- UNION で重複排除
SELECT email FROM customers UNION SELECT email FROM leads;

-- サブクエリ + DISTINCT で重複排除（同義）
SELECT DISTINCT email FROM (
    SELECT email FROM customers
    UNION ALL
    SELECT email FROM leads
) combined;