Selecting records from a many-to-many design

Question

There are three tables, Students, Classes, Student_Class

Is it possible to select students who are not registered in any classes without using NOT IN?

e.g. Select studentname from Students Where studentid NOT IN (Select Distinct studentid From Student_Class)

Answer 1

I really don't know why you don't want to use NOT IN, but here's one other option...

SELECT Studentname
FROM Students
LEFT JOIN Student_Class ON Student_Class.StudentID = Student.ID
WHERE Student_Class.StudentID IS NULL

Answer 2

Select studentname from Students s Where s.studentid NOT EXISTS (Select 1 From Student_Class sc where sc.studentid = s.studentid)

Answer 3

Thanks...I wasn't sure whether there'd be a performance issue with NOT IN...

Answer 4

There are three methods:

1. SELECT StudentName FROM Students WHERE StudentID NOT IN (SELECT StudentID FROM StudentClass);
2. SELECT StudentName FROM Students s WHERE NOT EXISTS (SELECT StudentID FROM StudentClass WHERE StudentID = s.StudentID); and
3. SELECT StudentName FROM Students s LEFT OUTER JOIN StudentClass sc ON s.StudentID = sc.StudentID WHERE sc.StudentID IS NULL).

And I really wouldn't use (3). So pick one.

WHERE EXISTS/WHERE NOT EXISTS is often preferred to IN/NOT IN. Modern optimizers will probably optimize (1) and (2) to being the same a lot of the time. It's worth comparing performance (when it matters).