为何SQL LEFT JOIN未按预期为每个左表类别返回额外NULL行？

问题背景 #

我在使用SQL LEFT JOIN时产生了困惑：原本期望categories表的所有记录都被返回，同时每个类别额外返回一条ProductName为NULL的行，但实际仅无匹配产品的类别（如豆类）显示NULL行，有产品的类别仅返回匹配的产品行，没有额外的NULL行。

数据情况 #

categories表 #

SELECT * FROM categories;

products表（示例） #

SELECT * FROM products; -- 仅展示部分示例，完整列表更长

我的查询语句 #

SELECT 
    Products.ProductName,
    Categories.CategoryName
FROM Categories
LEFT JOIN Products ON Products.CategoryID = Categories.CategoryID
ORDER BY CategoryName;

实际返回结果（示例） #

Chai       饮品
Chang      饮品
Ikura      海鲜
NULL       豆类

期望的结果 #

除了上述匹配的产品行，还希望每个类别额外返回一行ProductName为NULL的记录，最终结果应包含：

Chai       饮品
Chang      饮品
NULL       饮品
八角糖浆    调味品
Chef Anton的卡真调味料 调味品
Original Frankfurter grune Soae 调味品
NULL       调味品
...
NULL       豆类
...

核心疑问 #

我原本认为LEFT JOIN的逻辑是“始终包含左表的所有行”，所以每个类别至少有一行，同时不管有没有匹配产品，都要额外加一条ProductName为NULL的行。但实际并非如此，这是LEFT JOIN的正常工作机制吗？还是我对它存在根本性误解？

解答 #

这是你对LEFT JOIN的逻辑存在根本性误解，LEFT JOIN的核心规则是：

• 返回左表的每一行，对于右表（products）中能匹配到的记录，左表的一行会与右表的每一条匹配行组合成结果行；
• 只有当左表的某一行在右表中没有任何匹配项时，才会用NULL填充右表的列，生成一条结果行。

简单来说，LEFT JOIN不会为左表的每一行额外生成NULL行，而是左表行对应右表所有匹配行，无匹配时用NULL补全右表列。

如果你想要实现“每个类别既显示所有匹配产品，又额外显示一条ProductName为NULL的行”，可以用UNION ALL将原LEFT JOIN的结果和所有类别生成的NULL行合并：

-- 原有匹配结果
SELECT 
    Products.ProductName,
    Categories.CategoryName
FROM Categories
LEFT JOIN Products ON Products.CategoryID = Categories.CategoryID
UNION ALL
-- 每个类别添加的NULL行
SELECT 
    NULL AS ProductName,
    Categories.CategoryName
FROM Categories
ORDER BY CategoryName, ProductName;

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