如何查询authority_master全量权限及指定用户的匹配权限

我创建了SQL测试环境，表关系如下：

各表数据如下：

• userdetails表：
  
• authority_master表：
  
• users_authority_relation表：
  

我尝试了以下查询，但只能展示指定用户（user_id=1）已分配的权限，我需要获取authority_master表中所有权限记录，同时关联该用户的匹配权限。

SELECT U.first_name, 
       UR.authority_id as AUTHORITY_REL_AUTH_ID,
       AM.authority 
FROM userdetails U 
INNER JOIN users_authority_relation UR 
       ON U.user_id=UR.user_id 
LEFT JOIN authority_master AM 
       ON AM.authority_id=UR.authority_id 
WHERE U.user_id=1;

当前查询结果：

first_name   AUTHORITY_REL_AUTH_ID authority 
 admin                         1    ADMIN_USER
 admin                         2    STANDARD_USER
 admin                         4    HR_PERMISSION

期望输出（顺序无关）：

first_name   AUTHORITY_REL_AUTH_ID  authority
 admin                         1    ADMIN_USER
 admin                         2    STANDARD_USER 
admin                         null  NEW_CANDIDATE
 admin                         4    HR_PERMISSION

请问如何得到该期望输出？

要实现需求，需要调整表的连接顺序，以authority_master为主表（确保取出所有权限），再关联指定用户的信息，最后左连接用户权限关联表，这样未分配给该用户的权限会显示null。

正确的SQL语句如下：

SELECT 
    U.first_name,
    UR.authority_id AS AUTHORITY_REL_AUTH_ID,
    AM.authority
FROM authority_master AM
-- 关联指定用户（user_id=1）的信息，确保每条权限都带上该用户的名字
CROSS JOIN (SELECT first_name FROM userdetails WHERE user_id=1) U
-- 左连接用户权限关联表，匹配用户和权限的对应关系
LEFT JOIN users_authority_relation UR 
    ON AM.authority_id = UR.authority_id 
    AND UR.user_id = 1;

逻辑说明 #

1. 先从authority_master取出所有权限记录，这是结果集的基础；
2. 通过CROSS JOIN获取指定用户（user_id=1）的姓名，确保每条权限记录都能关联到该用户的信息；
3. 用LEFT JOIN连接users_authority_relation，并同时指定user_id=1和权限ID的匹配条件，这样当权限未分配给该用户时，UR.authority_id会返回null，正好符合期望输出。

执行该查询后，就能得到包含所有权限的结果，其中未分配给用户1的权限对应的AUTHORITY_REL_AUTH_ID为null。

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