LEFT JOIN 核心定義

回傳**左表所有行**，加上右表匹配的行。右表無匹配處填 NULL。LEFT JOIN = LEFT OUTER JOIN。

語法

SELECT select_list
FROM table1
LEFT 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
LEFT 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	Tove	NULL

關鍵觀察：Svendson 無訂單，但仍保留在結果中，OrderNo 為 NULL。

LEFT JOIN 核心應用場景

1. 統計類查詢 (含無資料分組)

-- 每個會員的訂單數與總消費 (含 0 筆會員)
SELECT m.id, m.username, 
       COUNT(o.id) AS order_count,
       COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM members m
LEFT JOIN orders o ON m.id = o.member_id AND o.status = 'completed'
WHERE m.status = 1
GROUP BY m.id, m.username
ORDER BY total_spent DESC;

2. 找出孤兒資料 (右表無匹配)

-- 找出無分類的商品
SELECT p.* FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE c.id IS NULL;

-- 找出無訂單的會員
SELECT m.* FROM members m
LEFT JOIN orders o ON m.id = o.member_id
WHERE o.id IS NULL;

3. 多對多關聯的中介表查詢

SELECT u.username, r.role_name
FROM users u
LEFT JOIN user_roles ur ON u.id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.id
WHERE u.status = 1;

LEFT JOIN 中 ON vs WHERE 關鍵差異

-- 情境：查詢會員及其「金額>1000」的訂單

-- ❌ 放在 WHERE：會過濾掉無訂單的會員 (變成 INNER JOIN)
SELECT m.*, o.order_no, o.amount
FROM members m
LEFT JOIN orders o ON m.id = o.member_id
WHERE o.amount > 1000;  -- o.amount 為 NULL 時條件為 UNKNOWN，行被過濾

-- ✅ 放在 ON：保留所有會員，只有匹配訂單且金額>1000 才顯示訂單
SELECT m.*, o.order_no, o.amount
FROM members m
LEFT JOIN orders o ON m.id = o.member_id AND o.amount > 1000;

多重 LEFT JOIN 陷阱

-- 會員 -> 訂單 -> 訂單項目 -> 商品
-- 若中間某層無匹配，後續層級全為 NULL
SELECT m.username, o.order_no, oi.qty, p.name
FROM members m
LEFT JOIN orders o ON m.id = o.member_id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id;

效能優化

1. 左表 WHERE 條件先過濾：減少驅動表行數
2. 右表關聯欄位建索引：INDEX(member_id) 在 orders 表
3. 避免 SELECT *：只查需要欄位
4. 大表 LEFT JOIN 小表較佳：小表驅動大表通常更優
5. 注意 NULL 參與運算：NULL + 1 = NULL，用 COALESCE(col, 0)

MySQL 8.0+ 新特性：LATERAL JOIN

-- 類似 CROSS APPLY，每行左表可關聯動態子查詢
SELECT m.username, recent_orders.*
FROM members m
LEFT JOIN LATERAL (
    SELECT * FROM orders 
    WHERE member_id = m.id 
    ORDER BY created_at DESC 
    LIMIT 3
) recent_orders ON true;

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, m.email,
           COUNT(o.id) AS order_count,
           COALESCE(SUM(o.total_amount), 0) AS total_spent,
           MAX(o.created_at) AS last_order_at
    FROM members m
    LEFT JOIN orders o ON m.id = o.member_id AND o.status = 'completed'
    WHERE m.status = 1
    GROUP BY m.id, m.username, m.email
    ORDER BY total_spent DESC
";

$result = $mysqli->query($sql);

echo "<table><tr><th>會員</th><th>訂單數</th><th>總消費</th><th>最後下單</th></tr>";
while ($row = $result->fetch_assoc()) {
    echo "<tr><td>" . htmlspecialchars($row['username']) . "</td>";
    echo "<td>" . $row['order_count'] . "</td>";
    echo "<td>¥" . number_format($row['total_spent'], 2) . "</td>";
    echo "<td>" . ($row['last_order_at'] ?? '無') . "</td></tr>";
}
echo "</table>";

$result->free();
$mysqli->close();
?>

總結

• LEFT JOIN 保留左表 100% 行，右表無匹配填 NULL
• 右表過濾條件**必須放在 ON 子句**，否則退化為 INNER JOIN
• 統計查詢用 COUNT(右表PK) + COALESCE(SUM(...),0)
• 找孤兒資料用 WHERE 右表PK IS NULL
• 多重 LEFT JOIN 注意 NULL 傳遞鏈