Except not working in mysql?

Question

I am trying to get id's from a specific table except from those ids from another table and it does not work:

SELECT id FROM table1 
EXCEPT 
SELECT id FROM table2

Feature request for EXCEPT: https://bugs.mysql.com/bug.php?id=1309 — Ben Creasy, Dec 29 '17 at 06:53
Possible duplicate of [Error when using except in a query](https://stackoverflow.com/questions/16092353/error-when-using-except-in-a-query) — underscore_d, Apr 17 '18 at 10:23

score 8 · Accepted Answer · answered Mar 23 '15 at 12:09

8

You can try this:

SELECT id FROM table1 
where id not in (SELECT id FROM table2)

MYSQL does not support EXCEPT

answered Mar 23 '15 at 12:09

Rahul Tripathi

score 1 · Answer 2 · answered Mar 23 '15 at 12:14

1

I don't think mysql supports EXCEPT. Instead try this way :

SELECT t1.id 
FROM table1 t1 
WHERE NOT EXISTS 
    ( SELECT 0 
      FROM table2 t2 
      WHERE t2.id = t1.id )

answered Mar 23 '15 at 12:14

Asit

score 1 · Answer 3 · edited Mar 23 '15 at 12:25

1

Use Not IN

SELECT `id` FROM `table1` 
WHERE `id` NOT IN (SELECT `id` FROM `table2`);

For more information you can take a look at MySQL NOT IN() function.

You can also use Left Join.

edited Mar 23 '15 at 12:25

Marcus Adams

answered Mar 23 '15 at 12:18

Kaushik

score 0 · Answer 4 · edited May 23 '17 at 11:58

0

there is no keyword as 'except' in mysql.

for the alternate way using not in and exists

edited May 23 '17 at 11:58

Community

answered Mar 23 '15 at 12:14

vijay

score 0 · Answer 5 · answered Sep 10 '18 at 10:59

0

You can use Left Join, as following:

SELECT t1.id 
FROM table1 AS t1 LEFT JOIN table2 AS t2 
    ON t1.id = t2.id
WHERE t2.id IS NULL

For more info, You can go to MySQL MINUS (or Except)

answered Sep 10 '18 at 10:59

5 Answers5