PostgreSQL - Find ids that do not exist in a table from a list

2.3k views Asked by At

I have a large list of IDs that I need to check against a table to find which ones are NOT in the table.

I have always queried to select the ones that are in table but not in the list like:

SELECT user_id FROM user_base WHERE user_id NOT IN ('22','33','88','99');

How do I do the opposite? Something like:

SELECT user_id FROM ('22','33','88','99') WHERE user_id NOT IN user_base;

2

There are 2 answers

0
Gordon Linoff On BEST ANSWER

You can use a left join:

select v.*
from (values (22), (33), (88), (99)) v(user_id) 
where not exists (select 1 from user_base ub where ub.user_id = v.user_id);
0
Kajin On

If you can add that large list of ids into a separate table, I believe this will do the job

SELECT ID FROM Table1 WHERE ID NOT IN (SELECT ID FROM Table2)