MySQL 多表關聯查詢全解析：JOIN 類型、語法與效能優化實戰

MySQL 多表關聯查詢全解析

JOIN 是關聯式資料庫的核心技能，掌握各類 JOIN 的語義差異與優化技巧，能讓查詢效能提升數倍。本文以 emp/dept 經典範例，系統整理 INNER/LEFT/RIGHT/CROSS/FULL JOIN 用法，並深入剖析 ON/WHERE 執行順序對效能的影響。

JOIN 分類速覽

實戰範例：emp/dept 關聯

-- 內連接：只顯示有部門的員工（14 筆）
SELECT e.empno, e.ename, e.sal, e.deptno, d.dname
FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;

-- 左連接：顯示所有員工，含無部門的 SCOTT（14 筆）
SELECT e.empno, e.ename, e.sal, e.deptno, d.dname
FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;

-- 右連接：顯示所有部門，含無員工的 OPERATIONS（14 筆）
SELECT e.empno, e.ename, e.sal, d.deptno, d.dname
FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno;

-- 全連接模擬：LEFT UNION RIGHT（6 筆）
SELECT * FROM A LEFT JOIN B ON A.name = B.name
UNION
SELECT * FROM A RIGHT JOIN B ON A.name = B.name;

效能優化三大鐵律

1. 顯式 INNER JOIN vs 隱式內連接：效能無差異

測試 10 萬筆資料，INNER JOIN ... ON 與 WHERE a.id=b.id 耗時幾乎相同，建議統一用顯式語法提升可讀性。

2. 能用 INNER JOIN 就別用 OUTER JOIN

OUTER JOIN 會產生額外 NULL 行，增加掃描與排序成本。業務允許時優先用 INNER JOIN。

3. 條件下推：盡量寫在 ON 而非 WHERE

-- 推薦：條件下推到 ON，減少中間結果集
SELECT * FROM A
INNER JOIN B ON B.name = A.name
LEFT JOIN C ON C.name = B.name AND C.status > 1
LEFT JOIN D ON D.id = C.id AND D.status = 1;

-- 不推薦：WHERE 過濾在 JOIN 完成後，中間結果集較大
SELECT * FROM A
INNER JOIN B ON B.name = A.name
LEFT JOIN C ON C.name = B.name
LEFT JOIN D ON D.id = C.id
WHERE C.status > 1 AND D.status = 1;

原理：ON 條件決定如何從右表取行；WHERE 在 JOIN 完成後再過濾。將過濾條件前置到 ON，能大幅縮減中間結果集。

4. 避免子查詢，改用 JOIN

-- 子查詢寫法（慢）
INSERT INTO t1(a1) SELECT b1 FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t1.id = t2.r_id);

-- JOIN 改寫（快）
INSERT INTO t1(a1)
SELECT b1 FROM t2
LEFT JOIN (SELECT DISTINCT id FROM t1) t1 ON t1.id = t2.r_id
WHERE t1.id IS NULL;

常見陷阱：ON vs WHERE 語義差異

-- ON 過濾：左表保留所有行，右表只有 id=2 的行參與匹配，其餘變 NULL
SELECT * FROM product LEFT JOIN product_details 
  ON product.id = product_details.id AND product_details.id = 2;
-- 結果：4 行（product 全保留）

-- WHERE 過濾：先 JOIN 再過濾，等同內連接
SELECT * FROM product LEFT JOIN product_details 
  ON product.id = product_details.id 
WHERE product_details.id = 2;
-- 結果：1 行（只有 id=2）

總結

• 熟練掌握 5 種 JOIN 語義，根據業務選型
• 優先用 INNER JOIN，條件下推到 ON 子句
• 複雜 OR/AND 條件善用 UNION 模擬 FULL JOIN
• 大表關聯務必建立覆蓋索引，EXPLAIN 驗證執行計劃