Syntax error when using except in a query

Question

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?

mccee · Accepted Answer · 2013-04-18 20:48:36Z

23

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'

edited Apr 18, 2013 at 20:48

answered Apr 18, 2013 at 20:31

mccee

1,66711 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Cratylus Over a year ago

You are right about not supporting EXCEPT. Your alternative though does not produce the correct result.

Cratylus Over a year ago

The query you posted does not return that.

mccee Over a year ago

I mocked up your data and got 5-10 returned. See modified answer.

Justin · Accepted Answer · 2013-04-19 14:01:43Z

9

Query:

SQLFIDDLEExample

SELECT s.sno 
FROM students s
WHERE NOT EXISTS (SELECT 0 
                  FROM take t 
                  WHERE t.sno = s.sno 
                  AND t.cno = 'CS112')

answered Apr 19, 2013 at 14:01

Justin

9,7336 gold badges38 silver badges49 bronze badges

1 Comment

Hossein Shahdoost Over a year ago

This is the best answer, since it works on multiple fields as well

Slowcoder · Accepted Answer · 2013-04-18 21:05:27Z

3

Try this.

SELECT s.sno FROM students s
LEFT JOIN take t ON t.sno = s.sno AND t.cno = 'CS112' 
WHERE t.sno IS NULL;

http://www.sqlfiddle.com/#!2/a7430/7

answered Apr 18, 2013 at 21:05

Slowcoder

2,1303 gold badges17 silver badges21 bronze badges

Collectives™ on Stack Overflow

Syntax error when using except in a query

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related