萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL FULL JOIN 全外連接完整教學:MySQL 模擬實現、聯集查詢與資料比對場景

FULL JOIN 核心定義

回傳**兩表所有行**,有匹配則合併,無匹配處填 NULL。相當於 LEFT JOIN UNION RIGHT JOIN (去重)。

MySQL 不支援 FULL JOIN

MySQL (含 8.0) **不支援** FULL JOIN / FULL OUTER JOIN 語法,執行會報語法錯誤。

MySQL 模擬 FULL JOIN 標準寫法

SELECT select_list
FROM table1
LEFT JOIN table2 ON table1.column = table2.column

UNION

SELECT select_list
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

關鍵點:使用 UNION (預設去重) 而非 UNION ALL,避免匹配行重複。

範例資料與查詢

Persons 表

P_IdLastNameFirstNameAddressCity
1HansenOlaTimoteivn 10Sandnes
2SvendsonToveBorgvn 23Sandnes
3PettersenKariStorgt 20Stavanger

Orders 表

O_IdOrderNoP_Id
1778953
2446783
3224561
4245621
53476415

模擬 FULL JOIN 查詢

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
LEFT JOIN Orders o ON p.P_Id = o.P_Id

UNION

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
RIGHT JOIN Orders o ON p.P_Id = o.P_Id
ORDER BY LastName;

結果

LastNameFirstNameOrderNo
HansenOla22456
HansenOla24562
PettersenKari77895
PettersenKari44678
SvendsonToveNULL
NULLNULL34764

包含:

  • 有匹配的行 (Hansen×2, Pettersen×2)
  • 左表獨有 (Svendson 無訂單)
  • 右表獨有 (OrderNo=34764 無會員)

進階模擬:避免 UNION 去重開銷

若確定無完全重複行,或允許重複,用 UNION ALL + 去重邏輯:

-- 方法:LEFT JOIN + 右表獨有行 (NOT EXISTS)
SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
LEFT JOIN Orders o ON p.P_Id = o.P_Id

UNION ALL

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Orders o
LEFT JOIN Persons p ON p.P_Id = o.P_Id
WHERE p.P_Id IS NULL;  -- 只取右表獨有

ORDER BY LastName;

實際應用場景

1. 雙向資料比對 / 對帳

-- 系統 A 與 系統 B 的交易記錄比對
SELECT 
    COALESCE(a.txn_id, b.txn_id) AS txn_id,
    a.amount AS sys_a_amount,
    b.amount AS sys_b_amount,
    CASE 
        WHEN a.txn_id IS NULL THEN '只在系統B'
        WHEN b.txn_id IS NULL THEN '只在系統A'
        WHEN a.amount = b.amount THEN '一致'
        ELSE '金額不符'
    END AS diff_status
FROM sys_a_transactions a
FULL JOIN sys_b_transactions b ON a.txn_id = b.txn_id
-- MySQL 寫法:
SELECT ... FROM sys_a_transactions a LEFT JOIN sys_b_transactions b ON a.txn_id = b.txn_id
UNION
SELECT ... FROM sys_a_transactions a RIGHT JOIN sys_b_transactions b ON a.txn_id = b.txn_id;

2. 合併兩張結構相似的歷史表

SELECT * FROM orders_2023
UNION
SELECT * FROM orders_2024
-- 這是 UNION,非 JOIN。FULL JOIN 用於「同主鍵不同屬性」合併

3. 維度表完整性檢查

-- 所有產品及其銷售記錄 (含從未銷售、及無產品資訊的銷售)
SELECT p.product_id, p.name, p.category, s.qty, s.sale_date
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
UNION
SELECT p.product_id, p.name, p.category, s.qty, s.sale_date
FROM products p
RIGHT JOIN sales s ON p.product_id = s.product_id
WHERE p.product_id IS NULL;

效能考量

方案優點缺點
UNION (去重)結果正確無重複需排序去重,大數據慢
UNION ALL + 過濾無排序開銷,快需確保邏輯正確不重複
應用層合併分頁查詢、記憶體合併應用層複雜、跨頁排序難

支援 FULL JOIN 的資料庫

  • PostgreSQL ✅
  • SQL Server ✅
  • Oracle ✅
  • SQLite ✅
  • MariaDB 10.7+ ✅ (FULL JOIN 支援)
  • MySQL ❌ (需模擬)

PHP 實作:資料比對報表

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->set_charset('utf8mb4');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// 模擬 FULL JOIN:本地訂單 vs 第三方支付記錄對帳
$sql = "
    SELECT 
        COALESCE(o.order_no, p.out_trade_no) AS trade_no,
        o.total_amount AS our_amount,
        p.total_fee / 100 AS pay_amount,
        o.status AS our_status,
        p.trade_state AS pay_status,
        CASE
            WHEN o.order_no IS NULL THEN '支付有單/我方無單'
            WHEN p.out_trade_no IS NULL THEN '我方有單/支付無單'
            WHEN o.total_amount = p.total_fee/100 THEN '金額一致'
            ELSE '金額不符'
        END AS diff
    FROM orders o
    LEFT JOIN payments p ON o.order_no = p.out_trade_no
    WHERE o.created_at >= '2024-01-01'
    
    UNION
    
    SELECT 
        COALESCE(o.order_no, p.out_trade_no),
        o.total_amount,
        p.total_fee / 100,
        o.status,
        p.trade_state,
        CASE
            WHEN o.order_no IS NULL THEN '支付有單/我方無單'
            WHEN p.out_trade_no IS NULL THEN '我方有單/支付無單'
            WHEN o.total_amount = p.total_fee/100 THEN '金額一致'
            ELSE '金額不符'
        END
    FROM orders o
    RIGHT JOIN payments p ON o.order_no = p.out_trade_no
    WHERE p.created_at >= '2024-01-01' AND o.order_no IS NULL
";

$result = $mysqli->query($sql);

echo "<table><tr><th>交易號</th><th>我方金額</th><th>支付金額</th><th>我方狀態</th><th>支付狀態</th><th>差異</th></tr>";
while ($row = $result->fetch_assoc()) {
    $diffClass = strpos($row['diff'], '不符') !== false ? 'style="color:red"' : '';
    echo "<tr>";
    foreach ($row as $val) {
        echo "<td $diffClass>" . htmlspecialchars($val ?? '-') . "</td>";
    }
    echo "</tr>";
}
echo "</table>";

$result->free();
$mysqli->close();
?>

總結

  • MySQL 不支援 FULL JOIN,標準模擬:LEFT JOIN UNION RIGHT JOIN
  • 核心用途:雙向資料比對、對帳、完整性檢查
  • UNION 自動去重,大數據考慮 UNION ALL + 邏輯過濾
  • MariaDB 10.7+ 原生支援 FULL JOIN
  • 應用層也可分頁查詢兩邊再合併,適合超大數據集
copyright © 萬盛學電腦網 all rights reserved