您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 多表資料合併查詢：UNION、JOIN 與臨時表實戰

編輯：MySQL 資料庫相關知識

場景描述

實務常見需求：兩張表分別儲存不同業務資料（如：orders 訂單表、region 區域表），需在單一結果集中並排顯示前 N 筆記錄，供前端一次渲染。

方案一：UNION ALL（最簡單、效能佳）

(SELECT customer_id AS col1, NULL AS col2 FROM orders LIMIT 2)
UNION ALL
(SELECT NULL, region_description FROM region LIMIT 2);

UNION ALL 不去重，比 UNION 快
欄位數、順序、型別必須一致，用 NULL 佔位
適用於「橫向拼接」不同來源同結構資料

方案二：JOIN 關聯（業務有關聯鍵時）

SELECT o.customer_id, r.region_description
FROM orders o
JOIN region r ON o.region_id = r.region_id
LIMIT 2;

需有共同關聯鍵（如 region_id）
結果為「縱向合併」，每行包含兩表資訊
最符合關聯式資料庫設計初衷

方案三：臨時表 + 游標（複雜邏輯、跨庫/異構來源）

原文範例使用 T-SQL（SQL Server）語法，MySQL 寫法如下：

DELIMITER $$
CREATE PROCEDURE merge_two_tables()
BEGIN
    DECLARE v_col1 VARCHAR(20);
    DECLARE v_col2 VARCHAR(20);
    DECLARE done INT DEFAULT 0;

    -- 臨時表
    CREATE TEMPORARY TABLE tmp_merge (
        id INT AUTO_INCREMENT PRIMARY KEY,
        col1 VARCHAR(20),
        col2 VARCHAR(20)
    );

    -- 兩個游標
    DECLARE cur1 CURSOR FOR SELECT customer_id FROM orders LIMIT 2;
    DECLARE cur2 CURSOR FOR SELECT region_description FROM region LIMIT 2;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur1;
    OPEN cur2;

    read_loop: LOOP
        FETCH cur1 INTO v_col1;
        FETCH cur2 INTO v_col2;
        IF done THEN LEAVE read_loop; END IF;

        INSERT INTO tmp_merge (col1, col2) VALUES (v_col1, v_col2);
    END LOOP;

    CLOSE cur1;
    CLOSE cur2;

    SELECT * FROM tmp_merge;
    DROP TEMPORARY TABLE tmp_merge;
END$$
DELIMITER ;

適用場景

兩表無關聯鍵，但需按順序配對（如：第 1 筆訂單對應第 1 筆區域）
來源為異構資料庫（透過 FEDERATED 引擎或應用層組裝）
需中間加工（清洗、轉換、計算）再合併

方案四：應用層組裝（現代架構推薦）

微服務架構下，建議由應用層並行查詢兩表，記憶體中組裝 JSON 回傳：

# Python pseudocode
orders = db.query("SELECT customer_id FROM orders LIMIT 2")
regions = db.query("SELECT region_description FROM region LIMIT 2")
result = [{"order": o, "region": r} for o, r in zip(orders, regions)]
return jsonify(result)

資料庫專注單表查詢，效能穩定
應用層水平擴展容易
邏輯清晰，易於單元測試

效能對比與選型建議

方案	適用場景	優點	缺點	建議指數
JOIN	有關聯鍵，需合併欄位	標準 SQL、優化器支援好	大表 JOIN 易慢	★★★★★
UNION ALL	同結構、不同來源垂直拼接	簡單、快、無去重開銷	需欄位對齊	★★★★☆
臨時表+游標	無關聯鍵、需順序配對、複雜邏輯	彈性最大	慢、佔資源、難維護	★★☆☆☆
應用層組裝	微服務、前端分離、高併發	解耦、擴展性強	網路往返、程式碼較多	★★★★★

MySQL 8.0 新功能：CTE 簡化臨時表

WITH cte1 AS (SELECT customer_id FROM orders LIMIT 2),
     cte2 AS (SELECT region_description FROM region LIMIT 2)
SELECT c1.customer_id, c2.region_description
FROM cte1 c1
JOIN cte2 c2 ON 1=1;  -- 無關聯鍵強制配對，實際需加 ROW_NUMBER()

MySQL 8.0 支援 ROW_NUMBER() OVER () 產生行號，可完美替代游標配對邏輯。