MySQL 多表連接查詢完全指南：LEFT/RIGHT/INNER JOIN 實戰解析

多表連接是 SQL 查詢的核心技能。本文通過具體資料集，圖解 LEFT JOIN (左連接)、RIGHT JOIN (右連接)、INNER JOIN (內連接) 的執行機制、結果差異與適用場景。

測試資料準備

CREATE TABLE user_id (id DECIMAL(18));
CREATE TABLE user_profile (id DECIMAL(18), name VARCHAR(255));

-- user_id: 8 筆 (含重複 id=1)
INSERT INTO user_id VALUES (1),(2),(3),(4),(5),(6),(1);

-- user_profile: 7 筆 (含重複 id=5, 且有 id=8 無對應)
INSERT INTO user_profile VALUES 
(1,'aa'),(2,'bb'),(3,'cc'),(4,'dd'),(5,'ee'),(5,'EE'),(8,'zz');

一、LEFT JOIN (左連接) - 以左表為主

SELECT a.id, IFNULL(b.name, 'N/A') AS name
FROM user_id a
LEFT JOIN user_profile b ON a.id = b.id;

結果 (8 筆) - 保留左表所有行

+------+------+
| id   | name |
+------+------+
| 1    | aa   |
| 2    | bb   |
| 3    | cc   |
| 4    | dd   |
| 5    | ee   |
| 5    | EE   |
| 6    | N/A  |  ← 右表無對應，NULL 補齊
| 1    | aa   |  ← 左表重複 id=1，各自匹配
+------+------+

三大匹配規則

1. 一對一：左表行在右表有唯一匹配 → 生成 1 行 (id 1,2,3,4)
2. 一對多：左表行在右表有 N 個匹配 → 生成 N 行 (id 5 → ee, EE)
3. 一對零：左表行在右表無匹配 → 生成 1 行，右側全 NULL (id 6 → N/A)

不符合以上規則的右表行 (如 id=8) 完全不會出現。

實戰應用：查找左表獨有資料

SELECT COUNT(*) 
FROM user_id 
LEFT JOIN user_profile ON user_id.id = user_profile.id 
WHERE user_profile.id IS NULL;

二、RIGHT JOIN (右連接) - 以右表為主

SELECT a.id, IFNULL(b.name, 'N/A') AS name
FROM user_id a
RIGHT JOIN user_profile b ON a.id = b.id;

結果 (7 筆) - 保留右表所有行

+------+------+
| id   | name |
+------+------+
| 1    | aa   |
| 2    | bb   |
| 3    | cc   |
| 4    | dd   |
| 5    | ee   |
| 5    | EE   |
| NULL | zz   |  ← 左表無 id=8，左側全 NULL
+------+------+

三大匹配規則 (鏡像對稱)

1. 一對一：右表行在左表有唯一匹配 (id 2,3,4,5)
2. 一對多：右表行在左表有 N 個匹配 (id 1 → 兩行)
3. 一對零：右表行在左表無匹配 → 生成 1 行，左側全 NULL (id 8)

左表獨有行 (id=6) 不會出現。

三、INNER JOIN (內連接) - 只保留雙方都有的

SELECT * FROM user_id a INNER JOIN user_profile b ON a.id = b.id;
-- 等價舊寫法
SELECT * FROM user_id a, user_profile b WHERE a.id = b.id;

結果 (7 筆) - 無 NULL 行

+------+------+------+
| id   | id   | name |
+------+------+------+
| 1    | 1    | aa   |
| 1    | 1    | aa   |
| 2    | 2    | bb   |
| 3    | 3    | cc   |
| 4    | 4    | dd   |
| 5    | 5    | ee   |
| 5    | 5    | EE   |
+------+------+------+

核心特性

• 結果集 絕無 NULL 值 (連接欄位雙方必須都有值)
• 可視為 LEFT/RIGHT JOIN 結果中 剔除含 NULL 行 後的交集
• MySQL 不支援 FULL OUTER JOIN，需用 UNION 模擬

四、三大連接類型核心對比

五、效能與最佳實踐

• 小表驅動大表：用較小結果集的表作驅動表 (LEFT JOIN 時左表應較小)
• 連接欄位必須建索引：ON a.id = b.id 兩側欄位均需索引
• 避免 SELECT *：只查詢必要欄位，減少網路傳輸與緩衝區壓力
• WHERE 條件下推：過濾條件寫在 WHERE 而非 ON 中 (內連接無差異，外連接有語義差異)
• EXPLAIN 分析：關注 type=ref/eq_ref、rows 預估行數

六、進階：模擬 FULL OUTER JOIN

SELECT * FROM user_id a LEFT JOIN user_profile b ON a.id = b.id
UNION
SELECT * FROM user_id a RIGHT JOIN user_profile b ON a.id = b.id;

結果包含所有 9 種組合 (左獨有、右獨有、雙向匹配)。