title: mysql select rows not in many-to-many relation
body: <p>I have a data structure where students and groups have many-to-many relationship. I have three tables</p>
  <blockquote>
  <p>students: id, name</p>
  <p>groups: id, name</p>
  <p>students_groups: student_id, group_id</p>
  </blockquote>
  <p>How do I select only students who are not in a specific group (e.g. group.id = 1)?</p>
  <p>I did some searching and tried using sub query but only get an empty set...</p>
  <pre><code>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);
  </code></pre>
  <p>how should I query? thx much in advance!</p>
  <p><strong>EDIT</strong>
  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???</p>
  <pre><code>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);
  </code></pre>