MySQL如何正确查询用户好友列表且排除用户自身记录

现有写法的错误点 #

• 过滤逻辑不全：WHERE条件仅筛选了friends.user_id = 1的记录，漏掉了friends.friend_id = 1的反向好友记录（用户2加你为好友的那条数据完全没进入查询范围），只能拿到你主动发起的好友关系，拿不到别人加你的记录
• 关联逻辑错误：JOIN用OR条件匹配两个字段，会导致每条好友记录同时匹配到你自己和好友两个用户的信息，返回重复数据，而且你需要取的是好友的用户名，不是发起好友申请的人的用户名，关联逻辑完全不符合需求
• 缺少自身过滤：没有添加排除用户自身的条件，导致自己的账号信息被匹配到结果里

正确实现方案 #

你需要先把所有和目标用户相关的好友记录全部捞出，再关联对应用户信息取好友的用户名，示例代码如下：

SELECT 
    t1.user_id,
    t1.friend_id,
    t2.username 
FROM friends AS t1 
LEFT JOIN users AS t2 
ON 
    -- 关联好友的user_id：如果当前记录是目标用户发起的，好友是friend_id，否则是user_id
    CASE 
        WHEN t1.user_id = 1 THEN t1.friend_id 
        ELSE t1.user_id 
    END = t2.user_id
WHERE 
    -- 捞出所有和目标用户相关的好友记录
    (t1.user_id = 1 OR t1.friend_id = 1)
    -- 排除自己成为自己好友的情况
    AND t2.user_id != 1

如果想要逻辑更直观也可以用UNION写法：

-- 目标用户主动添加的好友
SELECT t1.user_id, t1.friend_id, t2.username
FROM friends t1
JOIN users t2 ON t1.friend_id = t2.user_id
WHERE t1.user_id = 1
UNION
-- 其他人添加目标用户为好友的记录
SELECT t1.user_id, t1.friend_id, t2.username
FROM friends t1
JOIN users t2 ON t1.user_id = t2.user_id
WHERE t1.friend_id = 1

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