SQL左连接分组查询两个带星号结果均正确的原因咨询

基础表结构与预置测试数据 #

• users用户表：包含id（用户唯一ID）、name（用户名）、sex（性别）3个字段，共4条预置记录：
  • id=1，name=Ann，sex=NULL
  • id=2，name=Steve，sex='m'
  • id=3，name=Mary，sex='f'
  • id=4，name=Brenda，sex='f'
• friends好友关系表：包含user1、user22个字段，存储双向好友关系的双方用户ID，共3条预置记录：
  • (1,2)
  • (1,3)
  • (2,3)

待分析查询语句 #

SELECT users.name, COUNT(*) as count 
FROM users LEFT JOIN friends ON users.id = friends.user1 OR users.id = friends.user2
WHERE users.sex = 'f'
GROUP BY users.id, users.name;

结果拆解与底层逻辑 #

该查询的正确返回结果共2条：(Mary, 2)、(Brenda, 1)，执行逻辑严格遵循SQL标准执行顺序（FROM/JOIN -> WHERE过滤 -> GROUP BY分组 -> 聚合计算 -> SELECT返回），具体过程如下：

1. LEFT JOIN左连接阶段
   左表为users，右表为friends，连接匹配规则为：用户ID出现在好友关系的user1或user2字段中即算匹配成功。左外连接的核心规则是：左表所有记录都会保留，哪怕没有匹配到任何右表记录，对应行的右表字段会全部填充为NULL。
   这一步生成的临时中间表行数如下：

• Ann（id=1）：匹配到2条好友记录，生成2行
• Steve（id=2）：匹配到2条好友记录，生成2行
• Mary（id=3）：匹配到2条好友记录，生成2行
• Brenda（id=4）：未匹配到任何好友记录，保留1行，该行friends表相关字段全为NULL

2. WHERE条件过滤阶段
   过滤规则为users.sex = 'f'，会直接剔除中间表中不符合规则的行：

• Ann性别为NULL、Steve性别为'm'，对应的所有行全部被过滤删除
• Mary性别为'f'，对应的2行全部保留
• Brenda性别为'f'，对应的1行（无好友匹配的补NULL行）被保留

3. 分组聚合阶段
   按照users.id+users.name维度分组，对每个分组用COUNT(*)统计行数：

• Mary所属分组共包含2行有效记录，COUNT(*)只统计物理行数，不关心字段值是否为NULL，因此计数结果为2，对应返回结果(Mary, 2)
• Brenda所属分组共包含1行有效记录（就是左连接阶段保留的补NULL行），因此COUNT(*)计数结果为1，对应返回结果(Brenda, 1)

常见误区提醒：如果此处聚合函数写为COUNT(friends.user1)，Brenda对应行的friends.user1字段值为NULL，COUNT会自动忽略NULL值，最终返回0；但COUNT(*)不依赖任何字段值，只要行存在就计入统计，这是Brenda返回count=1的核心原因。

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