选择表中未出现在另一个表的外键中的所有项

Question

以具有用户的应用程序为例，每个用户可以恰好位于一个组中。如果我们想要选择没有成员的组列表，正确的SQL应该是什么？我一直觉得我就快掌握这个查询了，然后它又消失了。

加分-给出替代的senario，其中它是多对多配对，什么是SQL来识别未使用的组？

(如果您想要具体的字段名称：)一对多：

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

多对多：

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

Answer 1

没有成员的组(对于多对多配对)：

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

这个Sql也适用于您的“第一个”示例，因为您可以用子查询=)中的"users_groups“替换"users”。

就性能而言，我知道这个查询在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
) 

它将返回用户中不存在的所有组id