MySQL：如何筛选指定用户列表中无特殊角色的用户角色记录？

将现有两张表：user_roles表（含user_id、role_id字段）和roles表（含role_id、code_name字段）。需求：获取指定user_id列表中所有用户的user_roles记录，但需排除拥有code_name为'special_role'角色的用户。
示例数据如下：
user_roles表：

user_id	role_id
1	1
1	2
2	2
3	2

roles表：

role_id	code_name
1	special_role
2	another_role

我当前思路是使用临时表实现：

create temporary table if not exists all_user_ids as (
 select ur.user_id as user_id, ur.role_id as role_id
 from user_roles ur
 where ur.user_id in (1,2,3)
);
create temporary table if not exists special_user_ids as (
 select aui.user_id as user_id
 from all_user_ids aui
 join roles r on r.role_id = aui.role_id
 where r.code_name = 'special_role'
);
create temporary table if not exists non_special_user_ids as (
 select aui.user_id as user_id
 from all_user_ids aui
 where aui.user_id not in (select user_id from special_user_ids)
);
最终查询：
select ur.user_id, ur.role_id
 from user_roles ur
 where ur.user_id in (select user_id from non_special_user_ids)

但肯定有更优的实现方式，请教最佳方案？

你的临时表思路是可行的，但确实可以简化，避免多次创建临时表的开销，下面是几种更高效简洁的实现方案：

方案一：使用NOT EXISTS子查询（推荐，性能最优） #

NOT EXISTS 是数据库优化器非常友好的写法，它会在找到匹配的特殊角色记录时立即停止检查，性能表现出色：

SELECT ur.user_id, ur.role_id
FROM user_roles ur
WHERE ur.user_id IN (1, 2, 3)
  AND NOT EXISTS (
    SELECT 1
    FROM user_roles ur2
    JOIN roles r ON ur2.role_id = r.role_id
    WHERE ur2.user_id = ur.user_id
      AND r.code_name = 'special_role'
  );

逻辑说明：先筛选目标用户列表的所有角色记录，再排除那些存在special_role角色的用户。

方案二：LEFT JOIN + IS NULL #

这种写法通过左连接标记出拥有特殊角色的用户，再排除这些用户：

SELECT ur.user_id, ur.role_id
FROM user_roles ur
LEFT JOIN (
  -- 先找出所有拥有special_role的用户
  SELECT ur2.user_id
  FROM user_roles ur2
  JOIN roles r ON ur2.role_id = r.role_id
  WHERE r.code_name = 'special_role'
) special_users ON ur.user_id = special_users.user_id
WHERE ur.user_id IN (1, 2, 3)
  AND special_users.user_id IS NULL;

逻辑说明：左连接后，没有匹配到特殊用户的记录会显示NULL，通过这个条件筛选出非特殊用户的所有角色记录。

方案三：GROUP BY + HAVING聚合判断 #

适合需要基于用户角色聚合统计的场景，通过统计特殊角色的数量来排除目标用户：

SELECT ur.user_id, ur.role_id
FROM user_roles ur
JOIN roles r ON ur.role_id = r.role_id
WHERE ur.user_id IN (1, 2, 3)
GROUP BY ur.user_id, ur.role_id
HAVING SUM(CASE WHEN r.code_name = 'special_role' THEN 1 ELSE 0 END) = 0;

逻辑说明：按用户和角色分组后，统计每个用户是否有special_role，HAVING条件确保统计数为0（即无特殊角色）。

总结 #

这三种方案都比临时表写法更简洁，性能更优。其中方案一的NOT EXISTS通常是数据库优化器处理效率最高的，推荐优先使用。

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