Find IDs from a list that don't exist in a table

Question

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.

Answer 1

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;

Answer 2

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;

Answer 3

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:

• How to preserve the original order of elements in an unnested array?

There are a couple of basic techniques for the task:

• Select rows which are not present in other table

Answer 4

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