SQL Server三表关联查询：展示所有角色及特定用户的角色归属

我有三张表：tRole（角色表）、tUser（用户表）和关联表tRoleUser，需求是查询展示所有角色，并显示特定用户是否拥有该角色（用于“用户角色”页面的勾选状态）。目前用左外连接实现了需求，但想简化语句，能不能移除连接条件里的子查询？环境是SQL Server。

建表及测试数据 #

create table tRole (id int, name varchar(255))
insert into  tRole values (1,'user')
insert into  tRole values (2,'admin')

create table tRoleUser (roleId int, userId int)
insert into tRoleUser values (1,1)  -- 用户john
insert into tRoleUser values (1,2)  -- 用户sam
insert into tRoleUser values (2,2)  -- 管理员sam
-- amanda既不是普通用户也不是管理员

create table tUser (id int, name varchar(255))
insert into  tUser values (1,'john')
insert into  tUser values (2,'sam')
insert into  tUser values (3,'amanda')

-- 查看测试数据
select * from tRole
select * from tRoleUser
select * from tUser

现有解决方案 #

select 
    r.name 'role name'
    ,CASE WHEN ru.userid IS NULL THEN 'FALSE' ELSE 'TRUE' END UserInRole 
from tRole r
left outer join tRoleUser ru on 
    r.id = ru.roleid 
    and (ru.userid = (select id from tUser where name = 'john') or ru.roleid is null)

简化方案（移除连接条件中的子查询） #

方案1：先获取目标用户ID（适合参数化场景） #

先查询出目标用户的ID并赋值给变量，再带入主查询，彻底去掉连接条件里的子查询：

-- 替换为目标用户名，比如'sam'、'amanda'
DECLARE @targetUserName VARCHAR(255) = 'john'
DECLARE @targetUserId INT = (SELECT id FROM tUser WHERE name = @targetUserName)

SELECT 
    r.name AS 'role name',
    CASE WHEN ru.userId IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END AS UserInRole
FROM tRole r
LEFT JOIN tRoleUser ru 
    ON r.id = ru.roleId 
    AND ru.userId = @targetUserId

方案2：直接关联tUser表过滤 #

无需子查询，直接通过关联tUser表并指定用户名过滤，逻辑更直观：

SELECT 
    r.name AS 'role name',
    CASE WHEN ru.userId IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END AS UserInRole
FROM tRole r
LEFT JOIN tRoleUser ru 
    ON r.id = ru.roleId 
LEFT JOIN tUser u 
    ON ru.userId = u.id 
    AND u.name = 'john' -- 替换为目标用户名
GROUP BY r.name, ru.userId

方案3：使用EXISTS判断（更高效） #

通过EXISTS子句直接判断用户是否拥有该角色，语句简洁且性能较好：

SELECT 
    r.name AS 'role name',
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM tRoleUser ru
            JOIN tUser u ON ru.userId = u.id
            WHERE ru.roleId = r.id AND u.name = 'john' -- 替换为目标用户名
        ) THEN 'TRUE' 
        ELSE 'FALSE' 
    END AS UserInRole
FROM tRole r

查询结果示例 #

• 针对john的结果：

role name | UserInRole
----------|-----------
user      | TRUE
admin     | FALSE

• 针对amanda的结果：

role name | UserInRole
----------|-----------
user      | FALSE
admin     | FALSE

• 针对sam的结果：

role name | UserInRole
----------|-----------
user      | TRUE
admin     | TRUE

内容的提问来源于stack exchange，提问作者sam sergiy klok