回傳**兩表所有行**,有匹配則合併,無匹配處填 NULL。相當於 LEFT JOIN UNION RIGHT JOIN (去重)。
MySQL (含 8.0) **不支援**
FULL JOIN/FULL OUTER 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,避免匹配行重複。
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
| O_Id | OrderNo | P_Id |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 15 |
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;
| LastName | FirstName | OrderNo |
|---|---|---|
| Hansen | Ola | 22456 |
| Hansen | Ola | 24562 |
| Pettersen | Kari | 77895 |
| Pettersen | Kari | 44678 |
| Svendson | Tove | NULL |
| NULL | NULL | 34764 |
包含:
若確定無完全重複行,或允許重複,用 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;
-- 系統 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;
SELECT * FROM orders_2023
UNION
SELECT * FROM orders_2024
-- 這是 UNION,非 JOIN。FULL JOIN 用於「同主鍵不同屬性」合併
-- 所有產品及其銷售記錄 (含從未銷售、及無產品資訊的銷售)
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 支援)<?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();
?>
FULL JOIN,標準模擬:LEFT JOIN UNION RIGHT JOINUNION 自動去重,大數據考慮 UNION ALL + 邏輯過濾FULL JOIN