MySQL EXCEPT equivalent

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.

5 Answers

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.

use not in because except not available in mysql

select * from section where courseid not in ( select courseid from section where semester = 'Spring' and year= 2010 ) and semester = 'Fall' and year = 2009 

I would use not exists :

select s.* from section s where semester = 'Fall' and year = 2009 and not exists (select 1 from section s1 where s.course_id = s1.course_id and s1.semester = 'Spring' and s1.year = 2010 ); 

I'm fond of sticking with JOINs when possible, and it is certainly possible here.

SELECT sFa.course_id FROM section AS sFa LEFT JOIN section AS sSpr ON sFa.course_id = sSpr.course_id AND sSpr.semester = 'Spring' AND sSpr.year= 2010 WHERE sFa.semester = 'Fall' AND sFa.year= 2009 AND sSpr.course_id IS NULL ; 

SELECT DISTINCT s.course_id FROM section s WHERE s.course_id IN ( SELECT x.course_id FROM section x WHERE x.semester="Fall" AND x.year=2009 AND x.semester<>"Spring" AND x.year<>2010 );