MySQL选择不在多对多关系中的行

Question

我有一个数据结构，其中学生和组之间存在多对多的关系。我有三个表格：

学生：id、姓名

组：id、名称

学生_组：学生id、组id

如何仅选择不在特定组中（例如group.id = 1）的学生？

我进行了一些搜索并尝试使用子查询，但只得到了一个空集...

select * from students where not exists (select students.* from students left join students_groups on students_groups.student_id = student.id where students_groups.group_id = 1);

我应该怎样查询？非常感谢提前帮助！

编辑 好了，看起来以下两种方式终于有效了... 有人可以解释一下为什么我不需要连接表就能使其工作吗？

select * from students where not exists (select * from students_groups where students_groups.student_id = student.id and student_groups.group_id = 1);

select * from students where id not in (select student_id from students_groups where group_id = 1);

- minovsky

Answer 1

可以使用 NOT IN 来实现：

SELECT * FROM Students
WHERE Id NOT IN (
    SELECT Student_Id FROM Students_Groups
    WHERE Group_Id = 1)

- Bort

---

由于你只想找到不在第一组的任何学生，所以你选择了所有在第一组的学生的ID，并使用“NOT IN”来检查你选择的学生的ID是否不是其中之一。使用连接（join）也可以实现。 - Bort

你的答案为什么有效果我能理解，但是为什么我的带有join的初始答案不起作用呢？我只得到了一个空集。 - minovsky

@bort 我应该给你买个车牌。 - wootscootinboogie

你的初始答案不起作用，因为它基本上是说“选择所有学生记录，其中不存在具有Group_Id 1的Student_Groups记录”。你从未添加约束条件“AND student_id是我从Students表中选择的Id。”你需要在NOT EXISTS查询的where子句中加入该约束条件。 - Bort

@wootscootinboogie 别费心了，它们都已经卖完了。 - Bort

Answer 2

这个修改过的问题要求解释。

将SQL查询视为文本中的维恩图。每个子句都定义了一个内容圆圈，或告诉你你感兴趣的是重叠圆圈图的哪一部分。

select * from students where id not in (select student_id from students_groups where group_id = 1);

一个圆圈代表学生表。另一个圆圈代表student_groups表，其中group_id = 1。两个圆圈重叠的部分是指students.id等于student_groups.student_id的部分。您需要的是学生表中不在重叠区域的部分。

您无需连接这些表，因为结果集仅包含来自学生表的数据。您使用其他表来限制结果集，而不是提供结果数据。

- Dennis S.

Answer 3

未经测试，但以下其中之一应该可以。您需要进行一些解释并确定哪个最好。

select * 
from students 
where not exists (select *
                  from students_groups 
                  where students_groups.student_id = student.id 
                  and students_groups.group_id = 1)

or...

select * 
from students 
where id not in (select student_id
                  from students_groups 
                  where group_id = 1)

或者...

select students.id, students.name 
from students 
left outer join students_groups on students.id = students_groups.student_id
                                and students_groups.group_id = 1
where students_groups.student_id is null
group by students.id, students.name

- lc.

Answer 4

您可以尝试像这样的操作：

SELECT
    *
FROM
    students
WHERE
    id NOT IN
        ((SELECT 
            student_id
        FROM
            students_groups
        WHERE
            group_id = 1
        ))

- Brian Glaz