CODEWITHSUNDEEP
mysqlsqlexcept
ginos
ginos

Reputation: 97

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.

Upvotes: 1

Views: 4705

Answers (6)

lemon
lemon

Reputation: 15482

Since version MySQL 8.0.31 update, you can now use the EXCEPT operator natively. If you're allowed to update your MySQL version, you'll be able to run your code:

(select course_id
from section
where semester = 'Fall' and year= 2009)
except
(select course_id
from section
where semester = 'Spring' and year= 2010);

Upvotes: 1

Omkar Naik
Omkar Naik

Reputation: 1

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
 );

Upvotes: 0

Uueerdo
Uueerdo

Reputation: 15941

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
;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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.

Upvotes: 5

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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
                 );

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

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

Upvotes: 2

Related Questions