INNER JOIN 核心定義
只回傳**兩表皆有匹配**的行。無匹配的行被過濾掉。INNER JOIN = JOIN (關鍵字 INNER 可省略)。
語法
SELECT select_list
FROM table1
INNER JOIN table2
ON table1.column = table2.column
[WHERE condition];
範例資料與查詢
Persons 表
| P_Id | LastName | FirstName | Address | City |
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
Orders 表
| O_Id | OrderNo | P_Id |
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 15 |
查詢:列出所有有訂單的會員及其訂單號
SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
INNER JOIN Orders o ON p.P_Id = o.P_Id
ORDER BY p.LastName;
結果
| LastName | FirstName | OrderNo |
| Hansen | Ola | 22456 |
| Hansen | Ola | 24562 |
| Pettersen | Kari | 77895 |
| Pettersen | Kari | 44678 |
觀察:
- Svendson (P_Id=2) 無訂單,**不出現在結果中**
- Orders 中 P_Id=15 無對應會員,**不出現在結果中**
- Hansen 有 2 筆訂單,出現 2 行 (一對多展開)
多表 INNER JOIN
SELECT u.username, o.order_no, p.name AS product, oi.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE u.status = 1;
INNER JOIN vs WHERE 子查詢
-- 子查詢寫法 (舊版 MySQL 優化較差)
SELECT * FROM Persons WHERE P_Id IN (SELECT P_Id FROM Orders);
-- INNER JOIN 寫法 (推薦,可利用索引、優化器更好)
SELECT DISTINCT p.* FROM Persons p INNER JOIN Orders o ON p.P_Id = o.P_Id;
效能優化關鍵
- 關聯欄位雙向建索引:
INDEX(P_Id) 在兩表
- 型別長度字元集完全一致:避免隱含轉換
- 小結果集驅動大表:優化器通常自動選擇,可用
STRAIGHT_JOIN 強制順序
- 只選需要欄位:避免
SELECT *,利用覆蓋索引
- WHERE 條件下推:
WHERE 盡早過濾,減少 JOIN 參與行數
EXPLAIN 分析範例
EXPLAIN SELECT p.LastName, o.OrderNo
FROM Persons p INNER JOIN Orders o ON p.P_Id = o.P_Id;
-- 關注:
-- type: ref (理想) / ALL (全表掃描)
-- key: 實際使用的索引
-- rows: 預估掃描行數
-- Extra: Using where / Using index
常見陷阱
| 陷阱 | 現象 | 解決 |
| 結果行數暴增 | 一對多關聯導致行數乘積 | 用 DISTINCT、GROUP BY、或子查詢聚合後再 JOIN |
NULL 值匹配 | NULL != NULL,不會被 JOIN 匹配 | 用 COALESCE 或 IS NULL 處理 |
| 字元集不匹配 | utf8mb4 vs latin1 導致索引失效 | 統一字元集,ALTER TABLE ... CONVERT TO CHARSET utf8mb4 |
| 隱含轉換 | INT 欄位 JOIN VARCHAR 欄位 | 統一型別,或 CAST(col AS UNSIGNED) |
PHP 實作
<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->set_charset('utf8mb4');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$sql = "
SELECT m.id, m.username, o.order_no, o.total_amount, o.created_at
FROM members m
INNER JOIN orders o ON m.id = o.member_id
WHERE m.status = 1 AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 50
";
$result = $mysqli->query($sql);
echo "共 {$result->num_rows} 筆訂單記錄\n";
while ($row = $result->fetch_assoc()) {
printf("會員: %s | 訂單: %s | 金額: ¥%s | 時間: %s\n",
$row['username'], $row['order_no'], $row['total_amount'], $row['created_at']
);
}
$result->free();
$mysqli->close();
?>
總結
INNER JOIN = 交集,雙方必須有匹配
- 關聯條件放
ON,過濾條件放 WHERE
- 索引、型別一致、小表驅動大表是效能三要素
- 結果行數可能 > 原表行數 (一對多展開),注意後續處理
- MySQL 8.0+ 支援
LATERAL JOIN / CTE 更靈活處理複雜關聯