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

Answer 1

Using a NOT IN should work fine:

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

Answer 2

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.