CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1
Sagnik Sinha
Sagnik Sinha

Reputation: 873

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

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

Upvotes: 1

Views: 146

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659357

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:

Upvotes: 2

Related Questions