多对多外连接查询用户权限未返回未分配项问题排查

原有写法存在两个核心错误，导致无法返回未分配的权限记录：

• 关联逻辑顺序颠倒：连续使用RIGHT OUTER JOIN时，最终结果集的基准表是最后被连接的user表，只会保留用户已经通过user_permissions关联上的权限记录，无法覆盖permission表中所有未关联的权限条目。
• 过滤条件位置错误：将u.id = 1的判断写在WHERE子句中，外连接生成的未分配权限记录对应的u.id值为NULL，NULL = 1的逻辑判断结果为假，这类记录会被WHERE条件直接过滤，无法出现在最终结果中。

你的需求本质是以全量权限列表为基准，匹配指定用户已分配的权限，未匹配到的权限对应用户ID字段返回NULL，不需要复杂的多表右连，甚至不需要关联user表（因为查询目标用户ID是明确已知的），正确写法如下：

SELECT
  p.id AS permission_id,
  up.user_id
FROM permission p
LEFT JOIN user_permissions up
  ON p.id = up.permission_id
  AND up.user_id = 1

写法说明 #

• 以permission全表作为左表主表，天然保留全部4条权限记录
• 将用户ID的过滤条件写在LEFT JOIN的ON子句中，只会匹配用户1已有的权限关联记录；匹配不到的权限行，up.user_id会自动返回NULL，完全符合预期的返回结果。

如果需要关联user表获取用户的其他字段（比如firstName），可以用交叉连接先固定目标用户，再做左连，避免过滤掉NULL记录，示例：

SELECT
  p.id AS permission_id,
  u.id AS user_id,
  u.firstName
FROM permission p
CROSS JOIN (SELECT id, firstName FROM user WHERE id = 1) u
LEFT JOIN user_permissions up
  ON p.id = up.permission_id
  AND up.user_id = u.id

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