Why won't EXCEPT work for my query when using mysql server 5.7.22?

Question

So I am trying to find those manufacturers that sell Laptops, but not PC’s.

The schemas of the relations provided are: Product (maker, model, type), PC (model, speed, ram, hdisk, price), Laptop (model, speed, ram, hdisk, screen, price), and Printer (model, color, type, price)

I tried:

 (SELECT DISTINCT maker
 FROM Product P
 WHERE P.type=’Laptop’)
 EXCEPT
 (SELECT DISTINCT maker
 FROM Product P
 WHERE P.type=’PC’);

But I get the following error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXCEPT (SELECT DISTINCT maker FROM Product P WHERE P.type=’PC’)' at line 4

What am I doing wrong here? The EXCEPT in the query is underlined red in MySQL Workbench.

There is no `EXCEPT` in MySQL. Try a `NOT EXISTS` or a `NOT IN` subquery. — Paul Spiegel, Jul 29 '18 at 20:39
When I try a NOT EXISTS or a NOT IN as a replacement for EXCEPT, the NOT is underlined red so I can't run either query. — Growing My Roots, Jul 29 '18 at 22:24
I used EXCEPT because it's supposed to be the equivalent to difference or at least was at one time. Anybody know off the top of their head what is used for difference these days? — Growing My Roots, Jul 29 '18 at 22:29

score 1 · Answer 1 · answered Jul 29 '18 at 20:39

1

As far as i Know MySQL doesn't support EXCEPT syntax. Try using NOT IN or a LEFT JOIN:

(SELECT DISTINCT maker
FROM Product P
WHERE P.type=’Laptop’)
where marker not in
(SELECT DISTINCT maker
FROM Product P
WHERE P.type=’PC’);

answered Jul 29 '18 at 20:39

Mohammad

1,549
1
15
27

score 0 · Answer 2 · answered Jul 29 '18 at 20:47

As mentioned by @paul there is no

EXCEPT

in MySQL. Also you don't need the other subquery depending upon your database design.

SELECT DISTINCT maker FROM Product P WHERE P.type=’Laptop’

This will automatically only select product type that are only 'laptop' and not 'PC'. It all depends how efficiently you design your database.

Why won't EXCEPT work for my query when using mysql server 5.7.22?

2 Answers2