Select entries from a list that do not occur in the query result

Question

I use Postgres 9.1. I am running a query which is like

select count(distinct(user_id)) from users where user_id in (11,32,763,324,45,76,37,98,587);

Here the list of user_ids contains 600 entries. The result I am getting is 597. Thus there are 3 user_ids from the list, which are not present in the users. How do I get to know these 3 user_ids?

Please note that user_id is not the Primary Key of users

So `user_id` is the primary key of table `users`? – Erwin Brandstetter Jul 06 '15 at 11:29 — Erwin Brandstetter, Jul 06 '15 at 11:29
@ErwinBrandstetter, no it isn't – Sagnik Sinha Jul 08 '15 at 06:53 — Sagnik Sinha, Jul 08 '15 at 06:53

score 2 · Accepted Answer · edited May 23 '17 at 12:09

DISTINCT in your count-query only makes sense if user_id is not defined UNIQUE.
We don't need it either way for the query you ask for:

SELECT t.user_id
FROM   unnest('{11,32,763,324,45,76,37,98,587}'::int[]) t(user_id)
LEFT   JOIN users u USING (user_id)
WHERE  u.user_id IS NULL;

Beware of NOT IN if NULL values can be involved on either side of the expression! IN / NOT IN with a long value list also scales poorly.

Details:

Select entries from a list that do not occur in the query result

1 Answers1