Search code examples
sqlpostgresqlwhere-in

Find IDS not present in table

I have a bunch of md5 ids

6c26e28dc6484f0e998fd18b883de4c6
2bf4d0d85709c75adba13ba48011d62c
a67a5bcf329d58d2c23ed422214f66b3
...

Some of them are present in the table and some of them are new. How do I find which of these ids are not present in table If I do

select id from items where id not iN (...)

it then excludes all the ids

If I do

select id from items where id IN (...)

it only gives the ids that are present and I have to programmatically find the ones not present How do I directly get the IDS not present

Database: postgresql

Solution

  • You can use the VALUES clause which is a standard and available in most RDBMS:

    SELECT src.id
FROM (VALUES
    ('6c26e28dc6484f0e998fd18b883de4c6'),
    ('2bf4d0d85709c75adba13ba48011d62c'),
    ('a67a5bcf329d58d2c23ed422214f66b3')
) AS src(id)
WHERE NOT EXISTS (
    SELECT 1
    FROM items 
    WHERE items.id = src.id
)