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)
使用相同範例資料:
-- 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
SELECT *
FROM product
LEFT JOIN product_details
ON product.id = product_details.id
AND product_details.id = 2;
結果(4 行):左表 4 行全保留,右表僅 id=2 的行匹配,其餘 NULL。
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**。
NULL 的虛擬右表行。NULL,則將無匹配的左表行過濾掉。| 場景 | 條件放置位置 | 理由 |
|---|---|---|
| 關聯條件(主外鍵匹配) | ON | 語義清晰,利於優化器選擇驅動表 |
| 右表屬性過濾(如 status=1) | ON | 保留 LEFT JOIN 語義,無匹配行仍保留左表 |
| 左表屬性過濾 | WHERE | 不影響 JOIN 邏輯,可下推優化 |
| 需過濾掉無匹配行 | WHERE right.col IS NOT NULL | 明確轉為 INNER 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
ON 後的欄位雙方皆需索引。type(避免 ALL)、rows、Extra(Using where, Using join buffer)。LEFT JOIN 中 ON 決定「怎麼關聯」,WHERE 決定「保留哪些結果」。右表過濾條件放 ON 保留外連接語義,放 WHERE 會隱性轉內連接。掌握此核心差異,寫出正確且高效的多表查詢。