萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL LEFT JOIN 深度解析:ON vs WHERE 差異、三表聯查、避坑指南

LEFT JOIN 基本語義

SELECT columns
FROM left_table
LEFT JOIN right_table ON left_table.key = right_table.key;
  • **保留左表所有行**,右表無匹配則以 NULL 填充。
  • INNER JOIN 僅保留雙方匹配行;RIGHT JOIN 保留右表所有行。

直觀範例

-- 表 a
aid | adate
1   | a1
2   | a2
3   | a3

-- 表 b
bid | bdate
1   | b1
2   | b2
4   | b4

-- INNER JOIN
SELECT * FROM a INNER JOIN b ON a.aid = b.bid;
-- 結果:(1,a1,b1), (2,a2,b2)

-- LEFT JOIN
SELECT * FROM a LEFT JOIN b ON a.aid = b.bid;
-- 結果:(1,a1,b1), (2,a2,b2), (3,a3,NULL)

-- RIGHT JOIN
SELECT * FROM a RIGHT JOIN b ON a.aid = b.bid;
-- 結果:(1,a1,b1), (2,a2,b2), (NULL,b4,4)

核心陷阱:ON 條件 vs WHERE 條件

使用相同範例資料:

-- product: id=1,2,3,4; amount=100,200,300,400
-- product_details: id=2,4,5,6; weight=22,44,55,66; exist=0,1,0,1

案例 1:ON 附加條件

SELECT * 
FROM product 
LEFT JOIN product_details 
  ON product.id = product_details.id 
  AND product_details.id = 2;

結果(4 行):左表 4 行全保留,右表僅 id=2 的行匹配,其餘 NULL。

案例 2:WHERE 過濾條件

SELECT * 
FROM product 
LEFT JOIN product_details 
  ON product.id = product_details.id 
WHERE product_details.id = 2;

結果(1 行):先做 LEFT JOIN 產生 4 行,再用 WHERE 過濾,只剩右表 id=2 的那行,**LEFT JOIN 退化為 INNER JOIN**。

原理深度剖析

  1. ON 條件:決定**如何從右表檢索匹配行**。無匹配時,仍保留左表行並生成全 NULL 的虛擬右表行。
  2. WHERE 條件:在**匹配階段完成後**,對結果集進行過濾。若過濾條件涉及右表欄位且要求非 NULL,則將無匹配的左表行過濾掉。

最佳實踐原則

場景條件放置位置理由
關聯條件(主外鍵匹配)ON語義清晰,利於優化器選擇驅動表
右表屬性過濾(如 status=1)ON保留 LEFT JOIN 語義,無匹配行仍保留左表
左表屬性過濾WHERE不影響 JOIN 邏輯,可下推優化
需過濾掉無匹配行WHERE right.col IS NOT NULL明確轉為 INNER JOIN 語義

多表 LEFT JOIN 連鎖陷阱

SELECT * 
FROM a 
LEFT JOIN b ON a.id = b.a_id
LEFT JOIN c ON b.id = c.b_id
WHERE c.status = 1;  -- 陷阱!c 為 NULL 的行被過濾,導致前兩個 LEFT JOIN 失效

修正:將 c.status = 1 移至對應的 ON 子句:

LEFT JOIN c ON b.id = c.b_id AND c.status = 1

效能優化建議

  • **小表驅動大表**:左表應為較小結果集(經 WHERE 過濾後)。
  • **關聯欄位建索引**:ON 後的欄位雙方皆需索引。
  • **避免 SELECT ***:只選需要欄位,減少網路傳輸與臨時表大小。
  • **EXPLAIN 分析**:關注 type(避免 ALL)、rows、Extra(Using where, Using join buffer)。

總結

LEFT JOIN 中 ON 決定「怎麼關聯」,WHERE 決定「保留哪些結果」。右表過濾條件放 ON 保留外連接語義,放 WHERE 會隱性轉內連接。掌握此核心差異,寫出正確且高效的多表查詢。

copyright © 萬盛學電腦網 all rights reserved