Select users having required roles only

Question

I have standard table with users->roles relations

user_id | role_id
----------------
1       |   1
1       |   2
1       |   3
2       |   1
2       |   3
3       |   1
3       |   2
4       |   1
4       |   2
4       |   3
4       |   4

For the clear comprehension

1: [1, 2, 3]
2: [1, 3]
3: [1, 2]
4: [1, 2, 3, 4]

I want to write a query returning users having only required roles. For example, if required roles are [1, 2, 3, 5] then only users 1, 2, 3 satisfy the condition because user 4 has the role 4 not required

Answer 1

You can use correlated subquery

 select * from tablename a
 where not exists (select 1 from tablename b where a.user_id=b.user_id and role_id not in (1,2,3,5))