Mysql query to find IDs from a list that don't exist in a table

user_11993600 0
Viewed 1320

I'm trying to execute mysql query to find ids that dont exist in table but I'm getting an error. What am I doing wrong? Here is my query

create table scientist (id integer , firstname varchar(100), lastname varchar(100));
        insert into scientist (id, firstname, lastname) values (1, 'albert', 'einstein');
        insert into scientist (id, firstname, lastname) values (2, 'isaac', 'newton');
        insert into scientist (id, firstname, lastname) values (3, 'marie', 'curie');


select * from ( 
   VALUES ROW (1 , 2 , 3, 4, 5) 
)  as V
WHERE id not EXIST (select id from scientist);

I expect to see 4 and 5 in output

mysql sql database
1 Answers

You would use not exists like this:

select *
from (VALUES ROW (1) , ROW (2) , ROW (3), ROW (4), ROW (5)
     ) v(id)
where not exists (select 1 from scientist s where s.id = v.id);
user_11440350
Related