CODEWITHSUNDEEP
sqlsql-servert-sql
Yes - that Jake.
Yes - that Jake.

Reputation: 17129

How do I get rows not represented in many-to-many relationship?

I have a many-to-many relationship between students and classes as shown below. I would like to get the IDs of all the students who are not registered for any classes.

many-to-many relationship

Because of the size of the dataset, I'd like to avoid using NOT IN. Is there a more efficient solution?

Upvotes: 3

Views: 1025

Answers (5)

Martin Smith
Martin Smith

Reputation: 453707

You could also use

SELECT StudentID
FROM student 
EXCEPT
SELECT  StudentID
FROM student_class

Upvotes: 1

Ujjwal Manandhar
Ujjwal Manandhar

Reputation: 2244

The follwoing join query might result the answer

 SELECT student.id
 FROM student
 LEFT JOIN student_class ON student.studentid = student_class.studentid
 WHERE student_class.studentid IS NULL

Upvotes: 1

Johan
Johan

Reputation: 76723

An alternative is to use a left join:

SELECT s.student_id
FROM student s
LEFT JOIN student_class sc ON (sc.student_id = s.student_id)
WHERE sc.student_id IS NULL

Upvotes: 1

Paul Tomblin
Paul Tomblin

Reputation: 182832

select * from student
left join student_class on student_class.studentid = student.studentid
where student_class.scid is null;

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

NOT EXISTS should give you the best performance. See Left outer join vs NOT EXISTS for more details.

SELECT s.StudentID
    FROM student s
    WHERE NOT EXISTS(SELECT NULL
                         FROM student_class sc
                         WHERE sc.StudentID = s.StudentID)

Upvotes: 8

Related Questions