MySQL多表查询：如何在部分表无匹配时返回所有Stores和Items记录

我有三张表（Stores、Item和StoreInv），需要执行连接查询返回Stores和Items的所有记录组合，即便StoreInv中没有匹配的库存记录。

表结构示例 #

Stores表 #

StoreId
-------
Store1
Store2
Store3

Item表 #

ItemId
-------
A
B
C

StoreInv表（仅包含门店有库存的商品记录） #

ItemId    StoreId    Qty 
-------   -------    ---
A         Store1      6
B         Store1      2
B         Store2      4

期望输出 #

StoreId    ItemId   Qty 
-------    ------   ---
Store1     A         6
Store2     A         0 (或null)
Store3     A         0 (或null)
Store1     B         2
Store2     B         4
Store3     B         0 (或null)
Store1     C         0 (或null)
Store2     C         0 (或null)
Store3     C         0 (或null)

已尝试的SQL及错误结果 #

尝试的SQL语句：

SELECT str.StoreId, itm.ItemId, inv.Qty
FROM Item itm
LEFT JOIN StoreInv inv ON inv.ItemId = itm.ItemId
RIGHT JOIN Stores str on str.StoreId = inv.StoreId

得到的不符合预期的结果：

StoreId    ItemId   Qty 
-------    ------   ---
Store1     A         6
Store1     B         2
Store2     B         4
Store3     null      null

正确解法 #

要实现所有门店与商品的组合，首先需要生成Stores和Item的全量笛卡尔积，再通过左连接关联StoreInv表获取对应库存，最后用COALESCE函数将null值转换为0（如果需要）。

正确SQL语句：

SELECT 
    str.StoreId, 
    itm.ItemId, 
    COALESCE(inv.Qty, 0) AS Qty
FROM Stores str
CROSS JOIN Item itm
LEFT JOIN StoreInv inv 
    ON inv.StoreId = str.StoreId 
    AND inv.ItemId = itm.ItemId
ORDER BY itm.ItemId, str.StoreId;

说明 #

• CROSS JOIN会生成Stores和Item的所有记录组合（共3×3=9条），确保不会遗漏任何门店-商品对；
• LEFT JOIN StoreInv会保留所有笛卡尔积记录，匹配到库存的显示实际数量，未匹配到的显示null；
• COALESCE(inv.Qty, 0)将null替换为0，符合期望输出中的格式要求；
• 最后添加ORDER BY可以让结果按商品、门店排序，和示例输出一致。

内容的提问来源于stack exchange，提问作者Zek