Select All Items In A Table That Do Not Appear In A Foreign Key of Another Table

Question

Take for example an application which has users, each of which can be in exactly one group. If we want to SELECT the list of groups which have no members, what would be the correct SQL? I keep feeling like I'm just about to grasp the query, and then it disappears again.

Bonus points - given the alternative senario, where it's a many to many pairing, what is the SQL to identify unused groups?

(if you want concrete field names:) One-To-Many:

Table 'users': | user_id | group_id |
Table 'groups': | group_id |

Many-To-Many:

Table 'users': | user_id |
Table 'groups': | group_id |
Table 'user-group': | user_id | group_id |

Answer 1

For the first one, try this:

SELECT * FROM groups
LEFT JOIN users ON (groups.group_id=users.group_id)
WHERE users.user_id IS NULL;

For the second one, try this:

SELECT * FROM groups
LEFT JOIN user-group ON (groups.group_id=user-group.group_id)
WHERE user-group.user_id IS NULL;

Answer 2

Groups that have no members (for the many-many pairing):

SELECT *
FROM   groups g 
WHERE NOT EXISTS 
    (
      SELECT 1 
      FROM users_groups ug 
      WHERE g.groupid = ug.groupid
    );

This Sql will also work in your "first" example as you can substitute "users" for "users_groups" in the sub-query =)

As far as performance is concerned, I know that this query can be quite performant on Sql Server, but I'm not so sure how well MySql likes it..