CODEWITHSUNDEEP
sqlpostgresql
arctica
arctica

Reputation: 346

Default values for each key in a where-clause

Given a table:

ID VALUE
1  1
2  2

I need to set 0 for every ID in where-clause along with IDs:

select ... where ID in(1,2,3,4) ...

Result

ID VALUE
1  1
2  2
3  0
4  0

What query can I use here?

Upd1: let it be Postgres.

Upd2: can I do it without joins?

Upvotes: 0

Views: 32

Answers (1)

user330315
user330315

Reputation:

You didn't mention your DBMS, but the following is standard ANSI SQL:

select l.id, coalesce(t.value, 0) as value
from (
  values (1),(2),(3),(4)
) as l(id)
  left join the_table t on t.id = l.id;

It's possible without a join, but that doesn't make it prettier:

with input(id) as (
  values (1),(2),(3),(4)
)
select *
from the_table 
where id in (select id from input)
union all
select id, 0
from input
where id not in (select id from the_table)

Upvotes: 2

Related Questions