SELECT p.price, pd.name, m.manufacturers_name
FROM products p
LEFT JOIN products_description pd ON p.products_id = pd.products_id
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id;
以 user (左) 關聯 user_action (右) 為例:
user 表:
+----+------+
| id | name |
+----+------+
| 1 | libk |
| 2 | zyfon|
| 3 | daodao|
+----+------+
user_action 表:
+---------+--------+
| user_id | action |
+---------+--------+
| 1 | jump |
| 1 | kick |
| 1 | jump |
| 2 | run |
| 4 | swim |
+---------+--------+
SELECT u.id, u.name, a.action
FROM user u
LEFT JOIN user_action a ON u.id = a.user_id;
+----+--------+--------+
| id | name | action |
+----+--------+--------+
| 1 | libk | jump | -- 匹配 3 行 → 3 列結果
| 1 | libk | kick |
| 1 | libk | jump |
| 2 | zyfon | run | -- 匹配 1 行
| 3 | daodao | NULL | -- 無匹配 → NULL
+----+--------+--------+
-- 階段 1:匹配階段(只看 ON)
FROM A LEFT JOIN B ON A.id = B.id AND B.status = 1
-- B.status=1 參與匹配,不匹配則 B 欄位填 NULL
-- 階段 2:過濾階段(看 WHERE)
WHERE A.type = 2
-- 在匹配結果上過濾,可能過濾掉整行(含 NULL 行)
口訣: ON 決定「怎麼關聯」,WHERE 決定「保留哪些行」。
EXPLAIN SELECT ... FROM user u LEFT JOIN user_action a ON u.id = a.user_id;
-- 重點看:
-- type: ref / eq_ref (理想) vs ALL (全表掃描)
-- key: 使用的索引
-- rows: 預估掃描行數
-- Extra: Using where; Using index 等
ALTER TABLE user_action ADD INDEX idx_user_id (user_id);user 小、user_action 大,現狀佳;反之考慮 RIGHT JOIN 或交換順序SELECT * 回表INDEX idx_user_id_action (user_id, action) 讓查詢只走索引SELECT id FROM user LIMIT 10000, 20 再 JOIN 詳情| 錯誤寫法 | 問題 | 修正 |
|---|---|---|
| WHERE b.col = 'x' | 將 LEFT JOIN 退化為 INNER JOIN | 移至 ON: AND b.col = 'x' |
| SELECT * FROM A LEFT JOIN B ON A.id=B.id LEFT JOIN C ON A.id=C.id | 若 B、C 都大,中間結果爆炸 | 分步查詢或應用層組裝 |
| 未給關聯欄位加索引 | 全表掃描,鎖表、慢查詢 | 加索引 |