mysql select rows not in many-to-many relation

Question

I have a data structure where students and groups have many-to-many relationship. I have three tables

students: id, name

groups: id, name

students_groups: student_id, group_id

How do I select only students who are not in a specific group (e.g. group.id = 1)?

I did some searching and tried using sub query but only get an empty set...

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);

how should I query? thx much in advance!

EDIT OK, it seems the following two finally works... can anyone EXPLAIN to me why I don't need to join table for it to work???

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);

score 11 · Accepted Answer · answered Jul 19 '12 at 17:23

11

Using a NOT IN should work fine:

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

answered Jul 19 '12 at 17:23

Bort

7,398
3
33
48

Thanks Bort. I actually just figured out as well (see my edit). Can you please explain to me why I don't need to join the tables and I will select as answer. thanks! – minovsky Jul 19 '12 at 17:27
Since all you want to do is find any students who aren't in Group 1, you select the ids of all the students who **are** in Group 1, and use `NOT IN` to check that the ids of the students you are selecting aren't one of them. Using a join would also work. – Bort Jul 19 '12 at 17:30
makes sense why your answer works. but why didn't my initial answer with a join work? i only got an empty set – minovsky Jul 19 '12 at 17:36
@bort i should buy you a license plate. – wootscootinboogie Jul 19 '12 at 17:39
Your initial answer didnt work because it basically says "Select all student records where there doesn't exist a record in Student_Groups with Group_Id 1". You never added the constraint "AND the student_id is the Id I'm selecting from the Students table." You need that constraint in the where clause of your `NOT EXISTS` query – Bort Jul 19 '12 at 17:41
1

@wootscootinboogie Don't bother, they're all out. – Bort Jul 19 '12 at 17:41
Be careful using the `IN` clause if `Students_Groups` is large: https://stackoverflow.com/a/1069434/3806701 – cs_pupil Oct 29 '20 at 18:38

score 3 · Answer 2 · answered Jul 19 '12 at 17:41

The edited question asks for an explanation.

Think of SQL queries as Venn Diagrams in text. Each clause either defines a circle of content, or tells you which part of the full overlapping circles diagram you're interested in.

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

One circle is the students table. One circle is the student_groups table where group_id = 1. The circles overlap where students.id equals student_groups.student_id. You want the part of the students table that is not in the overlap area.

You don't need to join the tables because your result set contains data only from the students table. You are using the other table to limit that result set, not provide data to your results.

score 2 · Answer 3 · answered Jul 19 '12 at 17:22

Untested, but one of the following ought to work. You'll have to do some explaining and see which one is best.

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)

or...

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

score 2 · Answer 4 · answered Jul 19 '12 at 17:24

2

You could try something like this:

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

answered Jul 19 '12 at 17:24

Brian Glaz

15,468
4
37
55

mysql select rows not in many-to-many relation

4 Answers4

Linked