SQL 多表連接查詢完全指南：內連接、外連接、交叉連接語法與實例

多表連接是 SQL 查詢的核心技能。依據 SQL-92 標準，FROM 子句支援 join_table join_type join_table [ON (join_condition)] 語法。本文系統梳理三大連接類型、七種具體形式，並配合實例說明適用場景與注意事項。

一、連接類型總覽

大類	具體類型	關鍵字	結果集特徵
內連接	等值連接	INNER JOIN ... ON a.col = b.col	僅返回雙方匹配行，保留重複列
	自然連接	NATURAL JOIN	同名列自動匹配，刪除重複列
	不等連接	INNER JOIN ... ON a.col > b.col	非等值比較（>, <, >=, <=, <> 等）
外連接	左外連接	LEFT [OUTER] JOIN	左表全保留，右表無匹配則 NULL
	右外連接	RIGHT [OUTER] JOIN	右表全保留，左表無匹配則 NULL
	全外連接	FULL [OUTER] JOIN	雙表全保留，無匹配處填 NULL
交叉連接	笛卡爾積	CROSS JOIN	無 ON 條件，行數 = 左行數 × 右行數

二、內連接詳細實例

1. 等值連接 — 列出同城市的作者與出版社

-- 保留所有列（含重複 city、state）
SELECT * 
FROM authors AS a 
INNER JOIN publishers AS p ON a.city = p.city;

-- 自然連接風格：選擇列表去重
SELECT a.*, p.pub_id, p.pub_name, p.country
FROM authors AS a 
INNER JOIN publishers AS p ON a.city = p.city;

2. 不等連接 — 找出薪資高於部門平均值的員工

SELECT e.emp_name, e.salary, d.avg_salary
FROM employees e
INNER JOIN (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees GROUP BY dept_id
) d ON e.dept_id = d.dept_id AND e.salary > d.avg_salary;

三、外連接詳細實例

1. LEFT JOIN — 所有作者及其著作（無著作者也顯示）

SELECT a.au_id, a.au_lname, t.title
FROM authors AS a
LEFT JOIN titleauthor AS ta ON a.au_id = ta.au_id
LEFT JOIN titles AS t ON ta.title_id = t.title_id;

結果：左表 authors 所有行保留，右側無匹配顯示 NULL。

2. RIGHT JOIN — 所有書籍及其作者（無作者書籍也顯示）

SELECT t.title_id, t.title, a.au_lname
FROM authors AS a
RIGHT JOIN titleauthor AS ta ON a.au_id = ta.au_id
RIGHT JOIN titles AS t ON ta.title_id = t.title_id;

3. FULL OUTER JOIN — MySQL 不直接支援，用 UNION 模擬

SELECT * FROM authors a LEFT JOIN titleauthor ta ON a.au_id = ta.au_id
UNION
SELECT * FROM authors a RIGHT JOIN titleauthor ta ON a.au_id = ta.au_id;

四、交叉連接 CROSS JOIN

無 WHERE 條件時返回笛卡爾積。若 titles 6 類圖書，publishers 8 家出版社，則 6 × 8 = 48 行：

SELECT type, pub_name 
FROM titles CROSS JOIN publishers 
ORDER BY type;

實務上常配合 WHERE 過濾作為「生成組合」手段（如生成日期維度表）。

五、關鍵限制與最佳實踐

1. 不可直接連接 LOB 列：TEXT、NTEXT、IMAGE 不能直接出現在 ON 條件，需轉換（如 DATALENGTH()、SUBSTRING() 或 CHECKSUM()）。
2. 索引驅動連接：ON 欄位務必建立索引，否則退化為全表掃描（type=ALL）。
3. 優先使用內連接：內連接優化器選擇更多（如 Hash Join、Merge Join），外連接限制較多。
4. 避免隱式轉換：WHERE varchar_col = 123 導致索引失效，應寫 WHERE varchar_col = '123'。
5. 大表關聯分頁：先關聯取主鍵，再回表取明細（參考「延遲關聯」優化）。

六、MySQL 8.0+ 新特性：CTE 與側向連接

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) rn
    FROM employees
)
SELECT d.dept_name, r.emp_name, r.salary
FROM departments d
LEFT JOIN ranked r ON d.dept_id = r.dept_id AND r.rn <= 3;

遞歸 CTE 可替代舊版存儲過程處理樹狀結構，側向連接 LATERAL 則解決「每組取 Top N」難題。