I am looking for MySQL equivalent or equivalents for the following query:
(select course_id
from section
where semester = 'Fall' and year= 2009)
except
(select course_id
from section
where semester = 'Spring' and year= 2010);
where the section table is:
+-----------+--------+----------+------+----------+-------------+--------------+
| course_id | sec_id | semester | year | building | room_number | time_slot_id |
+-----------+--------+----------+------+----------+-------------+--------------+
| BIO-101 | 1 | Summer | 2009 | Painter | 514 | B |
| BIO-301 | 1 | Summer | 2010 | Painter | 514 | A |
| CS-101 | 1 | Fall | 2009 | Packard | 101 | H |
| CS-101 | 1 | Spring | 2010 | Packard | 101 | F |
| CS-190 | 1 | Spring | 2009 | Taylor | 3128 | E |
| CS-190 | 2 | Spring | 2009 | Taylor | 3128 | A |
| CS-315 | 1 | Spring | 2010 | Watson | 120 | D |
| CS-319 | 1 | Spring | 2010 | Watson | 100 | B |
| CS-319 | 2 | Spring | 2010 | Taylor | 3128 | C |
| CS-347 | 1 | Fall | 2009 | Taylor | 3128 | A |
| EE-181 | 1 | Spring | 2009 | Taylor | 3128 | C |
| EE-302 | 1 | Summer | 2010 | Watson | 327 | C |
| FIN-201 | 1 | Spring | 2010 | Packard | 101 | B |
| HIS-351 | 1 | Spring | 2010 | Painter | 514 | C |
| MU-199 | 1 | Spring | 2010 | Packard | 101 | D |
| PHY-101 | 1 | Fall | 2009 | Watson | 100 | A |
+-----------+--------+----------+------+----------+-------------+--------------+
In other words I want to find all courses taught in the Fall 2009 semester but not in the Spring 2010 semester.
MySQL doesn't support except, so just use not exists or not in:
select courseid
from section sf
where semester = 'Fall' and year = 2009 and not exists
(select 1
from section ss
where sf.courseid = ss.courseid and ss.semester = 'Spring' and ss.year = 2010
);
(I prefer not exists because it has more intuitive support for NULL values.)
This is not exact, because except removes duplicates. You could use select distinct, but I doubt that is really needed.