SELECT ... WHERE col1 in (input1, input2) how to know which not found?

SELECT col2 FROM table WHERE col1 in (input1, input2, input3)

If resulted in only one value, how to know which (input1, input2 or input3) were not found in col1?

It should be something like SELECT ? FROM (input1, input2, input3) WHERE ? NOT IN col1 FROM table, but couldn't find anything.

By the way, I use PostgeSQL.

1 answer

  • answered 2018-02-13 01:56 Abelisto

    with v(x) as (values(input1),(input2),(input3))
    select v.x from v except select col1 from table;
    

    or

    with v(x) as (values(input1),(input2),(input3))
    select v.x from v left join table on (v.x = table.col1)
    where table.col1 is null;
    

    or

    with v(x) as (values(input1),(input2),(input3))
    select v.x from v
    where not exists (select 1 from table where table.col1 = v.x)
    

    Last one is most efficient I think.

    Instead of values(input1),(input2),(input3) you could to use arrays:

    with v(x) as (select unnest(array[input1,input2,input3])) ...
    

    or

    with v(x) as (select unnest('{input1,input2,input3}'::input_type[])) ...
    

    to be able to pass the various number of input values from your application without rewrite the query.