基本語義

LEFT JOIN 以左表為基準，回傳左表所有列，右表無匹配則顯示 NULL。

視覺化範例

表 A (aid, adate)          表 B (bid, bdate)
1, a1                      1, b1
2, a2                      2, b2
3, a3                      4, b4

SELECT * FROM a LEFT JOIN b ON a.aid = b.bid;

結果：
aid adate  bid bdate
1   a1     1   b1
2   a2     2   b2
3   a3     NULL NULL   -- 右表無匹配

效能對比：LEFT JOIN vs 內連接/WHERE

-- LEFT JOIN（掃描左表全量）
SELECT COUNT(*) FROM ms_goods g LEFT JOIN ms_order_ogs og ON g.goods_id=og.goods_id;
-- 1,475 筆，0.0167s

-- 內連接/WHERE（僅匹配行）
SELECT COUNT(*) FROM ms_goods g, ms_order_ogs og WHERE g.goods_id=og.goods_id;
-- 257 筆，0.0088s

結論：只需匹配資料時，用 INNER JOIN 或 WHERE 更快；需保留左表全量才用 LEFT JOIN。

複雜報表範例：條件聚合

SELECT 
    StoreCode, GoodsCode,
    SUM(CASE WHEN GoodsFlag=2 THEN GoodsAmount ELSE 0 END) AS StockAmount,
    SUM(CASE WHEN GoodsFlag=4 THEN GoodsAmount ELSE 0 END) AS InAmount,
    SUM(CASE WHEN GoodsFlag=3 THEN GoodsAmount ELSE 0 END) AS OutAmount,
    SUM(CASE WHEN GoodsFlag=1 THEN GoodsAmount ELSE 0 END) AS SellAmount
FROM inventory_log
GROUP BY StoreCode, GoodsCode;

多表 LEFT JOIN 寫法規範

單一 LEFT JOIN

SELECT a.*, b.*, c.*
FROM b, a
LEFT JOIN c ON a.id = c.id
WHERE b.id = a.iid;
-- a 必須緊鄰其 LEFT JOIN 的表

多重 LEFT JOIN（需括號控制順序）

SELECT a.*, b.*, c.*, d.*, e.*
FROM ((
    SELECT * FROM c, b, a
    WHERE c.id = b.id AND b.id = a.id
) AS t1
LEFT JOIN d ON d.id = t1.a_id)
LEFT JOIN e ON e.id = t1.b_id;

優化檢查清單

1. 左表是否為小表/已過濾？
2. ON 條件欄位是否有索引？
3. 是否可改為 INNER JOIN？
4. SELECT 只取必要欄位，避免 *
5. 大表考慮分批、臨時表、應用層組裝

總結

LEFT JOIN 保留左表全量，代價是掃描更多行。只在「需保留無匹配行」時使用；其餘優先 INNER JOIN。多表連接務必用括號明確順序，ON 條件加索引。