MySQL JOIN 多表連接查詢全解析：內連接、左連接與 PHP 實戰

核心概念

• 內連接 (INNER JOIN)：僅返回兩表匹配的行
• 左連接 (LEFT JOIN)：返回左表所有行 + 右表匹配行，無匹配則 NULL 填充
• 右連接 (RIGHT JOIN)：左連接鏡像，少用

示例數據

-- tutorials_tbl
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
| 1           | Learn PHP      | John Poul       | 2007-05-24      |
| 2           | Learn MySQL    | Abdul S         | 2007-05-24      |
| 3           | JAVA Tutorial  | Sanjay          | 2007-05-06      |
+-------------+----------------+-----------------+-----------------+

-- tcount_tbl
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran          | 20             |
| mahnaz          | NULL           |
| Jen             | NULL           |
| Gill            | 20             |
| John Poul       | 1              |
| Sanjay          | 1              |
+-----------------+----------------+

內連接：僅匹配作者

SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
FROM tutorials_tbl a
INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
| 1           | John Poul       | 1              |
| 3           | Sanjay          | 1              |
+-------------+-----------------+----------------+

只有 John Poul、Sanjay 兩表都有，Abdul S 被過濾。

左連接：保留左表所有作者

SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
FROM tutorials_tbl a
LEFT JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
| 1           | John Poul       | 1              |
| 2           | Abdul S         | NULL           |
| 3           | Sanjay          | 1              |
+-------------+-----------------+----------------+

Abdul S 保留，tutorial_count 為 NULL。

PHP 實現

<?php
$mysqli = new mysqli('localhost', 'root', 'rootpassword', 'TUTORIALS', 3306);
if ($mysqli->connect_errno) { die('連接失敗: ' . $mysqli->connect_error); }

$sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count 
        FROM tutorials_tbl a 
        LEFT JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
$result = $mysqli->query($sql);
if (!$result) { die('查詢失敗: ' . $mysqli->error); }

while ($row = $result->fetch_assoc()) {
    echo "作者: {$row['tutorial_author']} | 數量: {$row['tutorial_count']} | ID: {$row['tutorial_id']}<br>";
}
$result->free();
$mysqli->close();
?>

實戰避坑指南

1. ON vs WHERE：LEFT JOIN ... ON a.id = b.id AND b.status=1（過濾右表）vs LEFT JOIN ... ON a.id = b.id WHERE b.status=1（變相變內連接）。
2. 選擇合適連接類型：需保留主表所有行 → LEFT JOIN；只需交集 → INNER JOIN。
3. 索引覆蓋連接列：ON a.col = b.col 兩側列均應建索引。
4. 避免 SELECT *：明確列出所需列，減少網絡傳輸。