Get courses chosen by all students

Question

Now there are two tables student(sid, name) and course(cid, name), which relation is many-to-many. So there is another table student_course(sid, cid) that stores the information about what courses have been chosen by whom.

How to write the sql that can get the courses that have been chosen by all the students?

Answer 1

Standard solution: use the NOT EXISTS ( ... NOT EXISTS (...)) construct:

• find all courses in which all students take part
• ==>> there must not exist a student that does not take part in this course

---

SELECT * FROM course c
WHERE NOT EXISTS (
        SELECT * from student s
        WHERE NOT EXISTS (
                SELECT * from student_course cs
                WHERE cs.sid = s.sid
                AND cs.cid = c.cid
                )
        )
        ;

This query is often faster (given appropiate indexes) than the count() == count() variant. The reason for this: you do not have to count all the (distinct) records; once you found one student that does not take this course you can omit this course from your list of suspects. Also: ANTI-joins often can make use of indexes [so can a count(), but that still has to count all the (distinct) keyvalues in the index]