SQL自连接ON子句关联顺序如何影响查询结果？

我们的需求是查询所有会员列表，同时展示推荐该会员的人员（若存在），并按会员的姓氏、名字排序。这是典型的自连接场景——同一张cd.members表既存储会员数据，也存储推荐人数据（推荐人本身也是会员）。

错误SQL的问题所在 #

你最初写的SQL里，ON条件是mems.memid = recs.recommendedby：

SELECT mems.firstname as memfname, mems.surname as memsname, recs.firstname as recfname, recs.surname as recsname
from 
    cd.members mems
    left outer join cd.members recs
        on mems.memid = recs.recommendedby
order by mems.surname, mems.firstname

这个条件的逻辑是：把当前会员的ID和另一行记录的推荐人ID做匹配，相当于在找「被当前会员推荐的人」，而不是「推荐当前会员的人」。比如会员A的recommendedby是会员B的ID，这个条件会去找recs.recommendedby等于A的ID的记录，也就是找A推荐的人，完全搞反了关联方向，自然拿不到正确的推荐人信息。

正确SQL的逻辑解析 #

正确的SQL里，ON条件是recs.memid = mems.recommendedby：

select mems.firstname as memfname, mems.surname as memsname, recs.firstname as recfname, recs.surname as recsname
from 
    cd.members mems
    left outer join cd.members recs
        on recs.memid = mems.recommendedby
order by memsname, memfname   

这个条件的逻辑是：

1. 以mems作为主表，遍历每一个会员（被推荐人）
2. 在recs表中查找会员ID等于当前会员的推荐人ID的记录——也就是推荐当前会员的人（因为mems.recommendedby字段存储的就是推荐人的会员ID）
3. 左外连接保证了即使某个会员没有推荐人（recommendedby为NULL），这条会员记录依然会被保留，推荐人相关字段显示为NULL，完全符合需求。

自连接的核心要点 #

自连接时一定要明确两张表的角色：

• mems是「被推荐的会员」角色
• recs是「推荐人」角色
  关联条件必须对应角色的字段关系：被推荐会员的recommendedby，对应推荐人的memid，这样才能正确匹配出每个会员的推荐人。

内容的提问来源于stack exchange，提问作者S. R.