Database has a users table with user_id as key, and a groups table with a composite key of group_id and user_id where either key can be repeated but the combination of the two is unique.

+---------------------+     +--------------------+
|       users         |     |       groups       |
+---------------------+     +--------------------+
| user_id | user_name |     | group_id | user_id |
+---------+-----------+     +----------+---------+
| 1       | admin     |     | 1        | 1       |
| 2       | ash       |     | 1        | 2       |
| 3       | birch     |     | 1        | 4       |
| 4       | cedar     |     | 1        | 5       |
| 5       | dogwood   |     | 2        | 3       |
+---------+-----------+     | 2        | 5       |
                            | 3        | 2       |
                            | 3        | 4       |
                            | 3        | 5       |
                            | 4        | 3       |
                            +----------+---------+

I am looking for the query which answers the question “which users are missing from each group?” The result set should be:

1 3
2 1
2 2
2 4
3 1
3 3
4 1
4 2
4 4
4 5

I can find users who don’t have any group:

SELECT users.user_id
FROM users 
LEFT JOIN groups 
ON users.user_id = groups.user_id
WHERE groups.user_id IS NULL

But if I try to further qualify with a group number it breaks. For example:

SELECT users.user_id, groups.group_id
FROM users 
LEFT JOIN groups 
ON users.user_id = groups.user_id
WHERE groups.user_id IS NULL
AND groups.group_id = 1

The result set I’m expecting from the above would be:

1 3

Ideally there would be a single query to return the desired result set but I would happily iterate over all the group IDs or user IDs with individual queries if that’s what it takes.

Real world database is PHPBB/MySQL. User names changed to trees because they don’t get upset when used as an example or complain their PII was leaked.