MySQL 連接查詢深度解析：LEFT/RIGHT/INNER JOIN 異同與避坑實戰

JOIN 是多表查詢核心，但 LEFT JOIN、RIGHT JOIN、INNER JOIN 在 ON 與 WHERE 條件下的語義差異極易導致業務 Bug。本文以兩表模型、實測數據對比、九組 SQL 對照，徹底講透三種連接的行為邊界與最佳實踐。

語法速查

-- LEFT JOIN：左表全量，右表匹配上則顯示值，否則 NULL
SELECT L.*, R.col FROM left_table L LEFT JOIN right_table R
  ON L.join_key = R.join_key AND R.filter_col = 'val'
  WHERE L.filter_col = 'val';

-- RIGHT JOIN：右表全量，左表匹配上則顯示值，否則 NULL
SELECT L.*, R.col FROM left_table L RIGHT JOIN right_table R
  ON L.join_key = R.join_key AND L.filter_col = 'val'
  WHERE R.filter_col = 'val';

-- INNER JOIN：僅保留雙方都匹配的行（等值連接）
SELECT L.*, R.col FROM left_table L INNER JOIN right_table R
  ON L.join_key = R.join_key
  WHERE L.filter_col = 'val' AND R.filter_col = 'val';
-- 等價舊語法：
SELECT L.*, R.col FROM left_table L, right_table R
  WHERE L.join_key = R.join_key
    AND L.filter_col = 'val' AND R.filter_col = 'val';

核心語義差異：ON vs WHERE

實測場景：左表 10 行（ID 1-10），右表 2 行（ID 1,6）

-- 基準：左表 ID<=6 的 6 行
SELECT ID, Username FROM left_table WHERE ID<=6;  -- SQL_1

-- LEFT JOIN 無右表額外條件
SELECT L.ID, L.Username, R.CollectNum, R.BuyNum
FROM left_table L LEFT JOIN right_table R ON L.ID=R.UID
WHERE L.ID<=6;  -- SQL_2：6 行，ID 2-5 右表列為 NULL

-- LEFT JOIN ON 帶右表條件 SearchNum>300
SELECT L.ID, L.Username, R.CollectNum, R.BuyNum
FROM left_table L LEFT JOIN right_table R ON L.ID=R.UID AND R.SearchNum>300
WHERE L.ID<=6;  -- SQL_3：6 行，ID 6 右表列也變 NULL（條件不滿足）

-- INNER JOIN 對應 SQL_2
SELECT L.ID, L.Username, R.CollectNum, R.BuyNum
FROM left_table L INNER JOIN right_table R ON L.ID=R.UID
WHERE L.ID<=6;  -- SQL_4：2 行（ID 1,6）

-- INNER JOIN 對應 SQL_3
SELECT L.ID, L.Username, R.CollectNum, R.BuyNum
FROM left_table L INNER JOIN right_table R ON L.ID=R.UID AND R.SearchNum>300
WHERE L.ID<=6;  -- SQL_5：1 行（ID 1）

-- 錯誤寫法：右表條件放 WHERE → 退化為 INNER JOIN
SELECT L.ID, L.Username, R.CollectNum, R.BuyNum
FROM left_table L LEFT JOIN right_table R ON L.ID=R.UID
WHERE L.ID<=6 AND R.SearchNum>300;  -- SQL_8：1 行（同 SQL_5）

-- 錯誤寫法：左表條件放 ON → 語義混亂但仍為 LEFT JOIN
SELECT L.ID, L.Username, R.CollectNum, R.BuyNum
FROM left_table L LEFT JOIN right_table R ON L.ID=R.UID AND L.ID<=6 AND R.SearchNum>300;  -- SQL_9：10 行（全左表），右表條件生效，左表條件失效

九組 SQL 對比總結

• SQL_2 vs SQL_4：唯一差別 LEFT vs INNER → 結果集 6 行 vs 2 行。LEFT 保左表全量，INNER 只保雙方匹配。
• SQL_3 vs SQL_5：同理，右表條件在 ON 中，LEFT 保左表行（右列 NULL），INNER 直接丟棄。
• SQL_4 vs SQL_6 / SQL_5 vs SQL_7：INNER JOIN 時，條件放 ON 或 WHERE **效果完全相同**。
• SQL_3 vs SQL_8：右表條件從 ON 移到 WHERE → LEFT JOIN **退化為 INNER JOIN**，結果集驟減，**極高風險 Bug**。
• SQL_2/3 vs SQL_9：左表條件錯放 ON → 左表過濾失效，返回全表，**極高風險 Bug**。

最佳實踐鐵律

1. 連接條件放 ON，業務過濾放 WHERE：ON L.key=R.key + WHERE L.status=1 AND R.type=2。
2. LEFT/RIGHT JOIN 時，被保留表的過濾條件必須在 WHERE；被匹配表的過濾條件放 ON（控制是否顯示 NULL）。
3. INNER JOIN 時，ON/WHERE 可互換，但建議統一放 WHERE 便於閱讀。
4. 避免 RIGHT JOIN：改用 LEFT JOIN + 交換表順序，降低認知負荷。
5. 複雜查詢用 CTE/子查詢預先過濾：WITH filtered AS (SELECT * FROM right_table WHERE SearchNum>300) SELECT ... LEFT JOIN filtered ...，語義清晰、優化器友好。

總結

JOIN 語義核心在於「誰全量保留、誰條件決定 NULL、誰條件決定丟棄」。LEFT JOIN 保左表，右表條件在 ON 控制 NULL/值，左表條件在 WHERE 控制留/捨。把這張真理表刻進肌肉記憶，配合 CTE 拆解複雜邏輯，JOIN 相關 Bug 將減少 90%以上。