JOIN 類型速覽

JOIN 類型	結果集	Venn 圖
INNER JOIN	兩表匹配的行	交集
LEFT JOIN	左表所有行 + 右表匹配行 (無匹配為 NULL)	左圈全保留
RIGHT JOIN	右表所有行 + 左表匹配行	右圈全保留
FULL OUTER JOIN	MySQL 不直接支援，用 UNION 模擬	兩圈全保留
CROSS JOIN	笛卡兒積	無條件全排列

實戰場景：推廣地點統計

三表：

• promotion_addr(id, name) 推廣地點
• sheet(id, promotion_addr_id) 報單
• unicom_record(id, sheet_id, date, status) 電話報竣

需求：統計 3 月已完成 (status='COMPLETE') 各地點報竣數。

標準寫法

SELECT 
    p.name,
    COUNT(*) AS complete_count
FROM unicom_record ur
LEFT JOIN sheet s ON ur.sheet_id = s.id
LEFT JOIN promotion_addr p ON s.promotion_addr_id = p.id
WHERE ur.status = 'COMPLETE'
  AND ur.date >= '2010-03-01'
  AND ur.date < '2010-04-01'
GROUP BY p.id, p.name
ORDER BY complete_count DESC;

JOIN 詳細範例 (emp / emp_dept)

-- 建表測試資料
CREATE TABLE emp (id INT PRIMARY KEY, name VARCHAR(10));
CREATE TABLE emp_dept (dept_id VARCHAR(4), emp_id INT, emp_name VARCHAR(10), PRIMARY KEY(dept_id, emp_id));

INSERT INTO emp VALUES 
(1,'Dennis-1'),(2,'Dennis-2'),(3,'Dennis-3'),(4,'Dennis-4'),(5,'Dennis-5'),
(6,'Dennis-6'),(7,'Dennis-7'),(8,'Dennis-8'),(9,'Dennis-9'),(10,'Dennis-10');

INSERT INTO emp_dept VALUES
('R&D',1,'Dennis-1'),('DEv',2,'Dennis-2'),('R&D',3,'Dennis-3'),
('Test',4,'Dennis-4'),('Test',5,'Dennis-5');

LEFT JOIN (以左表為基準)

SELECT a.id, a.name, b.dept_id
FROM emp a LEFT JOIN emp_dept b ON a.id = b.emp_id;
-- 結果: 10 行，emp_id 6-10 的 dept_id 為 NULL

查詢「僅在左表不在右表」

SELECT a.id, a.name
FROM emp a LEFT JOIN emp_dept b ON a.id = b.emp_id
WHERE b.dept_id IS NULL;
-- 6-10 共 5 行

RIGHT JOIN (以右表為基準)

SELECT a.id, a.name, b.dept_id
FROM emp a RIGHT JOIN emp_dept b ON a.id = b.emp_id;
-- 結果: 5 行 (僅有部門的員工)，順序按 emp_dept

INNER JOIN / 逗號 JOIN (等價)

-- 顯式 INNER JOIN
SELECT a.id, a.name, b.dept_id
FROM emp a INNER JOIN emp_dept b ON a.id = b.emp_id;

-- 隱式 JOIN (舊語法，不推薦)
SELECT a.id, a.name, b.dept_id
FROM emp a, emp_dept b
WHERE a.id = b.emp_id;
-- 結果同 INNER JOIN：5 行

效能優化關鍵

• 驅動表選擇：小表驅動大表，LEFT JOIN 左表為驅動表，RIGHT JOIN 右表為驅動表
• 關聯欄位索引：被驅動表關聯欄位**必須有索引** (emp_dept.emp_id)
• WHERE 條件下推：過濾條件寫在 WHERE 而非 ON，讓優化器先過濾驅動表
• 避免 SELECT *：只選需要欄位，利用覆蓋索引
• 大表關聯：考慮 STRAIGHT_JOIN 強制順序、或拆分為應用層多查詢

多表 JOIN 閱讀順序建議

SELECT 
    a.col1, b.col2, c.col3
FROM large_table a
INNER JOIN medium_table b ON a.id = b.a_id   -- 先縮小結果集
LEFT JOIN small_table c ON b.id = c.b_id     -- 再左連小表補全
WHERE a.status = 1
  AND b.type = 'A';

FULL OUTER JOIN 模擬

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL;

常見錯誤

錯誤	後果	修正
ON 條件寫錯欄位	結果錯誤/笛卡兒積	核對外鍵關係
LEFT JOIN 右表條件寫在 WHERE	變相變為 INNER JOIN	右表條件移至 ON 子句
未建索引關聯大表	極慢、全表掃描	建立被驅動表關聯欄位索引
濫用 RIGHT JOIN	閱讀困難、易出錯	統一用 LEFT JOIN，調整表順序