Select all the items in the table that are not displayed in the foreign key of another table

Question

Take, for example, an application in which there are users, each of which can be in the same group. If we want to CHOOSE a list of groups that have no members, what will be the correct SQL? I always feel that I am going to understand this question, and then disappears again.

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

(if you need specific 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

Groups that do not have members (for many, many mating):

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

Sql "" , "users" "users_groups" =)

, , Sql Server, , MySql .

Answer 2

:

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

:

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

Answer 3

SELECT * 
FROM groups
WHERE groups.id NOT IN (
    SELECT user.group_id 
    FROM user
) 

It will return the entire group identifier that is not present in the user