65

Say I have the following schema and data: 

create table images( id int not null ); insert into images values(1), (2), (3), (4), (6), (8);

I want to perform a query like:

select id from images where id not exists in(4, 5, 6);

But this doesn't work. The case above should return 5, since it doesn't exist in the table records.

Improve this question

4 Answers 4

Reset to default
78

You can use an outer join against a values list (similar to Martin's answer mentioned above):

select t.id from ( values (4),(5),(6) ) as t(id) left join images i on i.id = t.id where i.id is null;

or a not exists together with the row constructor:

select * from ( values (4),(5),(6) ) as v(id) where not exists (select * from images i where i.id = v.id);

If you like you can also put the values clause into a CTE to make the final query easier to read:

with v (id) as ( values (4),(5),(6) ) select v.id from v left join images i on i.id = v.id where i.id is null;
Improve this answer
2
  • Very good answer. To me, for a large list, all the () around the values -- e.g. (4) -- make it less clear. I do know how to edit them in fast, and they do seem to be REQUIRED. Commented Nov 2, 2021 at 17:42
  • 1
    Good answer indeed. But in mysql version > 8.0 we should use VALUES with ROW constructor: values (4),(5),(6) -> values row(4),row(5),row(6). dev.mysql.com/doc/refman/8.0/en/…. Commented Mar 15, 2022 at 12:07
22

One way of doing it would be to use VALUES to create a table expression with the ids to check and EXCEPT to find the missing ones.

SELECT id FROM (VALUES(4),(5),(6)) V(id) EXCEPT SELECT id FROM images;
Improve this answer
19

While using EXCEPT like @Martin provided, remember to make it EXCEPTALL, unless you want to pay a little extra for trying to fold duplicates.

BTW, a VALUES expression can stand on its own:

VALUES (4),(5),(6) EXCEPT ALL SELECT id FROM images;

But you get default column names this way.

For a long list of values it may be more convenient to provide it as array and unnest. Shorter syntax:

SELECT * FROM unnest('{4,5,6}'::int[]) id EXCEPT ALL SELECT id FROM images;

Allows to preserve the original order of elements by adding WITH ORDINALITY in a subquery. But this question does not ask for it. See:

There are a couple of basic techniques for the task:

Improve this answer
2

Just use a second table and join them. 

create table images1( id int not null ); create table images2( id int not null ); insert into images1 values(1), (2), (3), (4), (6), (8); insert into images2 values (4), (5), (6); SELECT i2.ID FROM images2 i2 LEFT JOIN images1 i1 ON i1.ID = i2.ID WHERE i1.ID IS NULL
Improve this answer
1
  • 6
    If you're executing a simple select and you're creating a table just for this it may not be the best solution. Commented Jun 13, 2016 at 22:38

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.