CODEWITHSUNDEEP
sqldatabasepostgresql
ryanpback
ryanpback

Reputation: 285

Is it possible to return the missing value of a WHERE IN query

I want to do some data validation. I have a sender_id and a receiver_id. I want to validate that each id I'm receiving is valid. I'm trying to avoid looping over the returned query and the list I'm comparing.

I have a users table with a primary id column, name, etc.

When a message is sent I was to grab the sender and receiver ids, make them into an array, and then query the DB as follows:

SELECT id FROM users WHERE id IN (1, 999);

In my test case, I know that id 999 doesn't exist.

What I'd like to do is make the IN query (or the query that makes this possible) and have it return 999 rather than 1 - or nothing if both exist. Is this possible?

Upvotes: 0

Views: 797

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Use a VALUES clause instead:

select *
from (values(1),(999)) my_ids(my_id)
where my_id not in (select id from users)
order by my_id;

Demo: https://dbfiddle.uk/?rdbms=postgres_8.4&fiddle=e0d88e36be9c42addf6a0c6357558bf7

Upvotes: 4

Andronicus
Andronicus

Reputation: 26026

What about:

select 999
from users
where id = 1;

This will return 999 if user exists. If not, there will be no result.

Upvotes: 1

Related Questions