SQL query to get all users not in group

Question

I have user and group tables set up as the following.

users
  id int

groups
  id int

users_groups
  user_id int
  group_id int

Example data:

+---------+----------+
| user_id | group_id |
+---------+----------+
| 1       | 1        |
| 1       | 2        |
| 2       | 1        |
| 3       | 2        |
| 3       | 3        |
| 4       | 1        |
| 4       | 2        |
| 5       | 2        |
+---------+----------+

Is there a way to select all users who are not in a given group? I tried joining the users and users_groups tables and adding a, say, group_id != 1 condition, but I end up getting the user when they are in another group, e.g., users [1, 3, 3, 4, 5].

So when I say I want users who are not in a given group, group_id != 1 in this case, the example results should be users [3, 5].

Answer 1

The simplest way to select the id of the users not in a given group is to use NOT IN:

SELECT id
FROM users
WHERE id NOT IN (SELECT user_id FROM users_groups WHERE group_id = 1)

Other commonly used alternatives are NOT EXISTS:

SELECT id
FROM users
WHERE NOT EXISTS (
    SELECT NULL
    FROM users_groups
    WHERE group_id = 1
    AND user_id = users.id
)

And LEFT JOIN / IS NULL:

SELECT id
FROM users
LEFT JOIN users_groups
ON users.id = users_groups.user_id
AND users_groups.group_id = 1
WHERE users_groups.user_id IS NULL

Answer 2

Try this:

SELECT * FROM users WHERE id 
  NOT IN (SELECT user_id FROM users_groups WHERE group_id = 1)

Answer 3

I'd use a "WHERE NOT EXIST" statement in this case.

Answer 4

SELECT a.* FROM users AS a
LEFT JOIN users_groups AS b ON b.user_id=a.id
WHERE b.group_id!=1
GROUP BY a.id

The keyword is GROUP BY

Answer 5

Try this:

SELECT *
FROM users u
 LEFT OUTER JOIN users_groups ug ON ug.user_id = u.user_id
    AND ug.group_id =1
WHERE ug.user_id IS NULL

Answer 6

You can use a query like:

Select Users.* 
from Users 
where id in 
  (Select user_id from users_groups where group_id <> given_group_id)