Get Roles which are still not assigned to member query

Question

How would I query MySQL database to return roles that are still not assigned (like available to him).

With table user_roles:

|user_id|role_id|
|   1   |   1   |
|   1   |   2   | 
|   1   |   4   |
|   1   |   7   |

How can I query roles table to return role name and id of all the roles that are not assigned. role_id: 3,5,8,...

Answer 1

Try

SELECT *
FROM roles r
WHERE NOT EXISTS (
   SELECT 1
   FROM user_roles ur
   WHERE ur.role_id = r.id
   AND ur.user_id = 1
)

Answer 2

I prefer to do this sort of set-complement query by using an outer join, instead of correlated or non-correlated subqueries.

SELECT r.id
FROM roles r
LEFT OUTER JOIN user_roles u 
  ON r.id = u.role_id AND u.user_id = 1
WHERE u.role_id IS NULL;

Answer 3

SELECT * 
FROM roles 
WHERE roles.id NOT IN (SELECT distinct role_id FROM user_roles)

if you want roles not assigned to user 1.

SELECT * 
FROM roles 
WHERE roles.id NOT IN (SELECT distinct role_id FROM user_roles WHERE NOT user_ID = 1)

Note: This is MUCH faster than the accepted answer, since it only runs the sub-query once and not once for every row in roles.