Syntax error when using except in a query

mysql, select, sql, sql-except

Solution

I don't believe MySQL supports EXCEPT syntax. Try using `NOT IN` or a `LEFT JOIN`:

SELECT s.sno 
FROM students s    
WHERE s.sno NOT IN 
(
    SELECT t.sno 
    FROM take t 
    WHERE t.cno = 'CS112'
);

OR

SELECT s.sno 
FROM students s    
    LEFT JOIN take t ON s.sno = t.sno
WHERE IFNULL(t.cno, '') != 'CS112'

UPDATE

I mocked up your data as such and it correctly returns 5 through 10:

create temporary table temp_students (sno int)

insert into temp_students values (1)
insert into temp_students values (2)
insert into temp_students values (3)
insert into temp_students values (4)
insert into temp_students values (5)
insert into temp_students values (6)
insert into temp_students values (7)
insert into temp_students values (8)
insert into temp_students values (9)
insert into temp_students values (10)

create temporary table temp_take (sno int, cno varchar(50))

insert into temp_take values (1, 'CS112')
insert into temp_take values (2, 'CS112')
insert into temp_take values (3, 'CS112')
insert into temp_take values (4, 'CS112')

SELECT s.sno 
FROM temp_students s    
        LEFT JOIN temp_take t ON s.sno = t.sno
WHERE IFNULL(t.cno, '') != 'CS112'

Problem

This query works: ``` mysql> SELECT s.sno FROM students s; +------+ | sno | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +------+ 10 rows in set (0.00 sec) ``` This query also works: ``` mysql> SELECT t.sno FROM take t WHERE t.cno = 'CS112'; +------+ | sno | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.00 sec) ``` BUT this query: ``` SELECT s.sno FROM students s EXCEPT SELECT t.sno FROM take t WHERE t.cno = 'CS112'; ``` fails with the error: ``` mysql> SELECT s.sno FROM students s -> EXCEPT -> SELECT t.sno FROM take t WHERE t.cno = 'CS112'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use n ear 'EXCEPT SELECT t.sno FROM take t WHERE t.cno = 'CS112'' at line 2 ``` What am I doing wrong here?

Original source