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

MySQL JOIN 語法解析與性能優化深度指南：內連接、外連接、交叉連接、全連接模擬

編輯：MySQL 綜合教學

JOIN 分類與語法


... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON condition
-- table1: 左表 (驅動表)；table2: 右表 (被驅動表)

1. INNER JOIN (內連接 / 等值連接)

僅返回兩表**匹配成功**的行。A ⋂ B


SELECT * FROM A INNER JOIN B ON A.name = B.name;
-- 結果: 僅 Pirate 和 Ninja 匹配行

2. LEFT JOIN (左外連接)

返回**左表所有行**，右表匹配列；無匹配則右表列為 NULL。A ⟕ B


SELECT * FROM A LEFT JOIN B ON A.name = B.name;
-- 結果: A 表 4 行全保留，Monkey/Spaghetti 右側為 NULL

等價寫法：LEFT OUTER JOIN (MySQL 中同義，**推薦用 LEFT JOIN**)

3. RIGHT JOIN (右外連接)

返回**右表所有行**，左表匹配列；無匹配則左表列為 NULL。A ⟖ B


SELECT * FROM A RIGHT JOIN B ON A.name = B.name;
-- 結果: B 表 4 行全保留，Rutabaga/Darth Vader 左側為 NULL

4. CROSS JOIN (交叉連接 / 笛卡爾積)

返回兩表所有行組合 (M × N 行)、**無 ON 條件**。


SELECT * FROM A CROSS JOIN B;  -- 4×4=16 行

MySQL 特有**：CROSS JOIN = INNER JOIN = JOIN (無 ON 時均為笛卡爾積；有 ON 時均為內連接)。標準 SQL 中 CROSS JOIN 不允許 ON。

5. FULL JOIN (全外連接) - MySQL 不直接支持

模擬方案：LEFT JOIN UNION RIGHT JOIN


SELECT * FROM A LEFT JOIN B ON A.name = B.name
UNION
SELECT * FROM A RIGHT JOIN B ON A.name = B.name;
-- 結果: 6 行 (含雙方無匹配行，對側為 NULL)

差集/對稱差集查詢 (實用技巧)


-- A 有 B 無 (左差集)
SELECT * FROM A LEFT JOIN B ON A.name = B.name WHERE B.id IS NULL;

-- B 有 A 無 (右差集)
SELECT * FROM A RIGHT JOIN B ON A.name = B.name WHERE A.id IS NULL;

-- 對稱差集 (全外連接減去內連接)
SELECT * FROM A LEFT JOIN B ON A.name = B.name WHERE B.id IS NULL
UNION
SELECT * FROM A RIGHT JOIN B ON A.name = B.name WHERE A.id IS NULL;

性能優化七大鐵律

1. 顯式 INNER JOIN vs 隱式 JOIN (逗號 + WHERE)


-- 顯式 (推薦，語義清晰，優化器友好)
SELECT * FROM a INNER JOIN b ON a.id = b.id;

-- 隱式 (舊寫法，功能等價，但易寫成笛卡爾積)
SELECT a.*, b.* FROM a, b WHERE a.id = b.id;

**結論** : 現代優化器對等價語句生成同樣執行計劃，**但顯式 JOIN 可讀性強、不易出錯，強制規範使用**。

2. 優先用 INNER JOIN，避免 LEFT/RIGHT JOIN + NULL 判斷

外連接產生 NULL 行，後續 WHERE col IS NULL 過濾，優化器難以下推 predicate，擴大中間結果集。**業務允許時，轉為 INNER JOIN 或應用層邏輯處理缺失**。

3. ON 條件 vs WHERE 條件：執行順序決定性能生死

鐵律：ON 用於「如何關聯/匹配」，WHERE 用於「最終過濾結果」。

匹配階段 (ON)：決定從右表取哪些行參與 JOIN。**此階段 WHERE 不生效**
過濾階段 (WHERE)：在匹配結果集上過濾

正確示範 (Predicate Pushdown 下推)：


-- 好：過濾條件下推到 ON，減少匹配行數
SELECT * FROM A
INNER JOIN B ON B.name = A.name
LEFT JOIN C ON C.name = B.name AND C.status > 1   -- 過濾下推到 ON
LEFT JOIN D ON D.id = C.id AND D.status = 1;      -- 過濾下推到 ON

-- 差：所有過濾留在 WHERE，先產生巨大中間結果
SELECT * FROM A
INNER JOIN B ON B.name = A.name
LEFT JOIN C ON C.name = B.name
LEFT JOIN D ON D.id = C.id
WHERE C.status > 1 AND D.status = 1;

4. ON 子句與 WHERE 子句語義差異 (外連接關鍵)


-- LEFT JOIN + ON 過濾：保留左表所有行，右表僅匹配 status=1 的行，不匹配右側為 NULL
SELECT * FROM product LEFT JOIN product_details 
ON (product.id = product_details.id) AND product_details.id = 2;

-- LEFT JOIN + WHERE 過濾：先 LEFT JOIN，再過濾 → 相當於 INNER JOIN
SELECT * FROM product LEFT JOIN product_details 
ON (product.id = product_details.id) 
WHERE product_details.id = 2;

5. 用 JOIN 替換子查詢 (NOT EXISTS → LEFT JOIN ... IS NULL)


-- 差：NOT EXISTS 相關子查詢，難優化
INSERT INTO t1(a1) SELECT b1 FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t1.id = t2.r_id);

-- 好：LEFT JOIN + IS NULL，可利用索引、Join Buffer、BKA 等優化
INSERT INTO t1(a1) 
SELECT b1 FROM t2 
LEFT JOIN (SELECT DISTINCT id FROM t1) t1 ON t1.id = t2.r_id 
WHERE t1.id IS NULL;