Courses Nobody Registered For in the Odd Semester
The academic office wants to drop courses that drew no registrations for the 2024-25 Odd semester.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #124
Problem statement
The academic office wants to drop courses that drew no registrations for the 2024-25 Odd semester. Registrations from other semesters do not count — a course popular last year but empty this semester must still be listed.
Return every course with no registration in the '2024-25 Odd' semester, with the columns course_code and title. Return the rows in any order.
Tables
Table: Course
| Column | Type |
|---|---|
| course_code | varchar |
| title | varchar |
| credits | int |
Primary key: course_code.
One row per course in the catalogue.
Table: Registration
| Column | Type |
|---|---|
| registration_id | int |
| roll_no | int |
| course_code | varchar |
| semester | enum(2023-24 Even, 2024-25 Odd, 2024-25 Even) |
Primary key: registration_id.
One row per student registering for a course in a semester; course_code is always in Course.
Examples
Example 1
Course
| course_code | title | credits |
|---|---|---|
| CS301 | Operating Systems | 4 |
| CS302 | Computer Networks | 4 |
| MA201 | Probability and Statistics | 3 |
| HS101 | Professional Communication | 2 |
| EC210 | Digital Electronics | 3 |
Registration
| registration_id | roll_no | course_code | semester |
|---|---|---|---|
| 1 | 2101 | CS301 | 2024-25 Odd |
| 2 | 2102 | CS301 | 2024-25 Odd |
| 3 | 2101 | MA201 | 2023-24 Even |
| 4 | 2103 | CS302 | 2024-25 Odd |
| 5 | 2104 | HS101 | 2024-25 Even |
| 6 | 2102 | MA201 | 2023-24 Even |
Output
| course_code | title |
|---|---|
| MA201 | Probability and Statistics |
| HS101 | Professional Communication |
| EC210 | Digital Electronics |
How to solve Courses Nobody Registered For in the Odd Semester
This is an anti join with a twist: only registrations of one semester count. The natural form is a LEFT JOIN from Course to Registration and a test for the missing partner, r.registration_id IS NULL.
The semester condition must sit in the ON clause. There it limits which registrations may match, so a course with registrations only in other semesters finds no partner and comes through with NULLs. Written in WHERE instead, r.semester = '2024-25 Odd' is evaluated after the join; on the unmatched rows r.semester is NULL, the condition is unknown, and every course you wanted is filtered away — while a course with only old registrations is dropped too.
NOT EXISTS with the semester inside the subquery says the same thing directly, and NOT IN is safe here because course_code is never NULL in Registration. All three read each course once and probe the registrations by course code.
Reference solution (MySQL)
SELECT c.course_code, c.title
FROM Course c
LEFT JOIN Registration r
ON r.course_code = c.course_code AND r.semester = '2024-25 Odd'
WHERE r.registration_id IS NULLAnother way
SELECT course_code, title FROM Course c WHERE NOT EXISTS (SELECT 1 FROM Registration r WHERE r.course_code = c.course_code AND r.semester = '2024-25 Odd')Another way
SELECT course_code, title FROM Course WHERE course_code NOT IN (SELECT course_code FROM Registration WHERE semester = '2024-25 Odd')← Students Short of 75% Attendance in a Course · Fee Invoices Paid Late or Still Overdue →