您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 實現 FULL JOIN：LEFT JOIN + UNION + RIGHT JOIN 替代方案

編輯：MySQL 綜合教學

MySQL 實現 FULL JOIN：LEFT JOIN + UNION + RIGHT JOIN 替代方案

MySQL 不支援標準 SQL 的 FULL OUTER JOIN。標準替代方案：LEFT JOIN UNION RIGHT JOIN。本文提供標準語法、去重邏輯、效能考量與進階變體。

標準替代語法

SELECT * 
FROM A 
LEFT JOIN B ON A.id = B.id
WHERE <條件>

UNION

SELECT * 
FROM A 
RIGHT JOIN B ON A.id = B.id
WHERE <條件>;

關鍵點解析

組件	作用	注意
LEFT JOIN	保留 A 全部，匹配 B	A 無匹配時 B 欄位為 NULL
RIGHT JOIN	保留 B 全部，匹配 A	B 無匹配時 A 欄位為 NULL
UNION	合併結果集，自動去重	若需保留重複行用 `UNION ALL`
WHERE 條件	建議兩側一致	或移至 ON 子句避免語義差異

完整範例

-- 表 A：用戶
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO users VALUES (1,'Alice'),(2,'Bob'),(3,'Charlie');

-- 表 B：訂單
CREATE TABLE orders (id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2));
INSERT INTO orders VALUES (101,1,99.00),(102,1,199.00),(103,4,50.00);  -- user_id=4 不存在

-- FULL JOIN 替代：所有用戶 + 所有訂單
SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id

UNION

SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 結果：
-- user_id | name    | order_id | amount
-- 1       | Alice   | 101      | 99.00
-- 1       | Alice   | 102      | 199.00
-- 2       | Bob     | NULL     | NULL
-- 3       | Charlie | NULL     | NULL
-- NULL    | NULL    | 103      | 50.00  -- 孤兒訂單

進階變體

1. UNION ALL + 去重標記（效能更好，避免 UNION 排序去重開銷）

SELECT 'LEFT' AS src, u.id, u.name, o.id, o.amount
FROM users u LEFT JOIN orders o ON u.id = o.user_id

UNION ALL

SELECT 'RIGHT' AS src, u.id, u.name, o.id, o.amount
FROM users u RIGHT JOIN orders o ON u.id = o.user_id
WHERE u.id IS NULL;  -- 只取 RIGHT 獨有的（孤兒記錄）

2. COALESCE 合併鍵欄位（單一鍵欄位輸出）

SELECT COALESCE(u.id, o.user_id) AS user_id, u.name, o.id AS order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id

UNION

SELECT COALESCE(u.id, o.user_id) AS user_id, u.name, o.id AS order_id, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;