CODEWITHSUNDEEP
sqlsqlitecount
heisthere
heisthere

Reputation: 372

sqlite: How to return 0 when no row found within `in` operator

I asked a same question before, it was answered and solved using psql how to return 0 instead of null with 'in' operator. This was the idea of the solution, using a built list instead in:

select * from
(
    values (1), (2), (3)
) as required_ids ( text_id );

But I somehow have to rewrite it by sqlite3, the problem is that, there is no values(...) thing in sqlite3, so I cannot build a list instead of in(...). Does anyone know how to do that? Thanks a lot!!

Upvotes: 0

Views: 156

Answers (2)

forpas
forpas

Reputation: 164139

there is no values(...) thing in sqlite3

Of course there is, since version 3.15:

select * from (values (1), (2), (3));

You can refer to the returned column as column1.
Result:

> | column1 |
> | ------: |
> |       1 |
> |       2 |
> |       3 |

Or inside a CTE:

with cte(val) as (
  select * from (values (1), (2), (3))
)
select * from cte

Result:

> | val |
> | --: |
> |   1 |
> |   2 |
> |   3 |

See the demo.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can use select/union all:

select *
from (select 1 as text_id union all select 2 union all select 3) required_id

Upvotes: 1

Related Questions