如何用NOT IN子句筛选完全不包含指定角色ID的用户

Let’s start by clarifying why your original query isn’t giving you the results you want: the NOT IN clause returns any row where the role_id isn’t in your target list. But if a user has some roles from the list and some that aren’t, those non-list role rows will still show up for that user—hence you’re getting users like 3256 and 62 in your results, which isn’t what you need.

You need to check that a user has no entries at all in the user_group_role table for any of those role IDs. Here are two reliable, efficient approaches:

Approach 1: Use NOT EXISTS #

This method directly checks if there’s no record of the user having any of the target roles. It’s clean and performs well for most datasets:

SELECT DISTINCT user_id
FROM user_group_role ugr
WHERE NOT EXISTS (
    SELECT 1
    FROM user_group_role ugr2
    WHERE ugr2.user_id = ugr.user_id
    AND ugr2.role_id IN (23,98,105,3310,4928,4929,4930)
);

• The subquery looks for any matching target role ID for the same user. If none exist, the user is included in the results.
• DISTINCT ensures we only get each user once, even if they have multiple non-target roles (like user 899 with role 41).

Approach 2: Group with HAVING #

This approach aggregates user roles and counts how many of them are in your target list. If the count is 0, the user has none of the specified roles:

SELECT user_id
FROM user_group_role
GROUP BY user_id
HAVING SUM(CASE WHEN role_id IN (23,98,105,3310,4928,4929,4930) THEN 1 ELSE 0 END) = 0;

• The CASE statement marks each target role as 1 and non-target roles as 0. Summing these values gives the total number of target roles the user has.
• A sum of 0 means the user has no matching roles, so they’re included in the output.

Testing with Your Sample Data #

Both queries will return:

• User 4 (only has role 54, no target roles)
• User 899 (only has role 41, no target roles)

Users like 3256 (has all target roles) and 62 (has some target roles) will be excluded, which matches your desired outcome.

Bonus: Include Users with No Roles At All #

If you have a separate users table and want to include users who don’t have any roles in user_group_role at all, use a left join:

SELECT u.user_id
FROM users u
LEFT JOIN user_group_role ugr ON u.user_id = ugr.user_id
GROUP BY u.user_id
HAVING SUM(CASE WHEN ugr.role_id IN (23,98,105,3310,4928,4929,4930) THEN 1 ELSE 0 END) = 0;

This ensures users with no role entries are still considered (their sum will be 0, so they meet the condition).

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