Search code examples
sqlpostgresqlperformanceoptimizationquery-optimization

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!

Solution

  • 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)