Search code examples
sqlpostgresqlin-subquery

Multiple values IN

Normally IN is used with one value:

SELECT * FROM data WHERE f1 IN (<subquery>)

It is possible to use it with multiple values:

SELECT * FROM data WHERE f1 IN (<subquery>) OR f2 IN (<subquery>);

But can I remove duplication, something like:

SELECT * FROM data WHERE ANY(f1, f1) IN (<subquery>)

I tried to use CTE, but it also require subquery.

Solution

  • While both solutions works, I have found that fastest (unfortunately not shortest) way is to use CTE:

    WITH x AS (<subquery>)
SELECT * FROM data WHERE f1 IN (SELECT * FROM x) OR f2 (SELECT * FROM x)