MySQL 多表聯接（JOIN）完全指南：內連接、外連接、多表鏈式 JOIN 與鍵列關係建模

MySQL 多表聯接（JOIN）完全指南：內連接、外連接、多表鏈式 JOIN 與鍵列關係建模

關係型數據庫通過主鍵與外鍵建立表間關係，JOIN 在單條查詢中組合多表數據。理解內連接（INNER JOIN）與外連接（LEFT/RIGHT JOIN）的語義差異是寫出正確 SQL 的前提。

關係建模示例

• vendors 表：vend_id（主鍵）、vend_name...
• products 表：prod_id（主鍵）、vend_id（外鍵引用 vendors）...

1. 內連接：僅返回匹配行

等值連接寫法（舊語法）：

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id;

標準 INNER JOIN 寫法（推薦）：

SELECT vend_name, prod_name, prod_price
FROM vendors
INNER JOIN products ON vendors.vend_id = products.vend_id;

2. 多表鏈式 JOIN

需求：查詢訂單 20005 包含的產品名、價格、廠商名。

三表關係：orders → orderitems（外鍵 order_num） → products（外鍵 prod_id） → vendors（外鍵 vend_id）。

SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems
INNER JOIN products ON orderitems.prod_id = products.prod_id
INNER JOIN vendors ON products.vend_id = vendors.vend_id
WHERE orderitems.order_num = 20005;

理解技巧：WHERE 條件可視為過濾器，JOIN ... ON 確定連接鍵，先在腦中畫出 ER 圖再寫 SQL。

3. 外連接：保留單側所有行

• LEFT JOIN：保留左表所有行，右表無匹配填 NULL。
• RIGHT JOIN：保留右表所有行。

例：列出所有廠商及其產品（無產品廠商也顯示）：

SELECT vend_name, prod_name
FROM vendors
LEFT JOIN products ON vendors.vend_id = products.vend_id;

性能與最佳實踐

• 連接列必須建立索引，否則大表關聯觸發全表掃描（Nested Loop Join 極慢）。
• 驅動表（外層循環）盡量小，被驅動表（內層）大且有索引。MySQL 優化器會自動選擇，必要時用 STRAIGHT_JOIN 強制順序。
• 避免 SELECT *，僅選取必要列，減少網路傳輸與緩存壓力。
• 外連接條件放在 ON 子句，而非 WHERE，否則退化為內連接。