SQL多角色查询中未匹配目标值时返回null的实现方法

你需要的效果可以通过左连接或者条件聚合实现，两种写法都能保证每个用户仅返回1行，无重复数据，且未持有对应角色时对应列自动返回NULL：

方案1：左连接关联角色表（逻辑最直观） #

SELECT 
    u.USER_NAME,
    r1.USER_ROLE AS ROLE_MASTER,
    r2.USER_ROLE AS ROLE_ADMIN
FROM SYSTEM.USERS u
-- 左连接匹配MASTER角色
LEFT JOIN SYSTEM.USER_ROLE r1 
    ON u.USER_NAME = r1.USER_NAME 
    AND r1.USER_ROLE = 'MASTER'
-- 左连接匹配ADMIN角色
LEFT JOIN SYSTEM.USER_ROLE r2 
    ON u.USER_NAME = r2.USER_NAME 
    AND r2.USER_ROLE = 'ADMIN'
-- 如果需要仅返回至少持有MASTER角色的用户，放开下方注释
-- WHERE r1.USER_ROLE IS NOT NULL

逻辑说明 #

左连接的特性是匹配不上条件时对应表的所有字段返回NULL，完全符合你要的「未持有角色时列显示空值」的需求，且每个用户最多返回1行，不会出现重复数据。

方案2：条件聚合写法（适合需扩展更多角色校验的场景） #

如果后续需要校验超过2个角色，这种写法扩展更方便，不需要新增JOIN：

SELECT 
    u.USER_NAME,
    MAX(CASE WHEN ur.USER_ROLE = 'MASTER' THEN ur.USER_ROLE END) AS ROLE_MASTER,
    MAX(CASE WHEN ur.USER_ROLE = 'ADMIN' THEN ur.USER_ROLE END) AS ROLE_ADMIN
FROM SYSTEM.USERS u
LEFT JOIN SYSTEM.USER_ROLE ur ON u.USER_NAME = ur.USER_NAME
GROUP BY u.USER_NAME
-- 如果需要仅返回至少持有MASTER角色的用户，放开下方注释
-- HAVING MAX(CASE WHEN ur.USER_ROLE = 'MASTER' THEN 1 ELSE 0 END) = 1

原写法问题说明 #

• 第一种写法使用隐式内连接，只有同时满足两个角色匹配条件的用户才会返回，自然过滤掉了仅持有单个角色的用户。
• 第二种写法新增OR条件后，会触发笛卡尔积导致同一用户返回多行重复数据，不符合你的去重要求。

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