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开发者_Python百科 ANY(f1, f1) IN (<subquery>)

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

Assuming you have columns col1,col2, and col3 and that you're looking for col1 and col2 in your subquery (for the sake of illustration), you can do something like:

select col1,col2,col3
from table
where (col1,col2) in (<subquery>)
group by 1,2,3;

As long as <subquery> is of the (rough) form select col1,col2 from <blah>, then you'll end up with distinct (col1,col2,col3) triples where (col1,col2) pairs appear in <subquery>.

I might write this in terms of set operators:

SELECT *
FROM data
WHERE EXISTS (
    VALUES(f1, f2)

    INTERSECT

    SELECT(<subquery>)
)

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)