CODEWITHSUNDEEP
sqlpostgresqlperformanceoptimizationquery-optimization
Forece85
Forece85

Reputation: 518

PostgreSQL check if values in a given list exist in a table

Given below table in Postgres:

id some_col
1 a
1 b
2 a
3 a

I want to get output as id and true (if at least one row with that id is present in the table) or false (if no rows with that id are found in the table).

For example where id in (1, 2, 3, 4, 5):

id value
1 true
2 true
3 true
4 false
5 false

I tried with the group by and case with conditions, but is there any optimized/performance SQL way of achieving this? Bcz, groupby does complete rows count which is unnecessary in this scenario. And, with exists query I didn't find a way to deal with multiple rows return;

Thanks in Advance!

Upvotes: 2

Views: 3083

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272006

The in clause won't do it. You need to build a list of values somehow (the values table valued constructor works in most rdbms) and outer join with it:

SELECT x.id, EXISTS (
    SELECT *
    FROM t
    WHERE t.id = x.id
) AS value
FROM (VALUES (1), (2), (3), (4), (5)) AS x(id)

Upvotes: 4

Related Questions