CODEWITHSUNDEEP
sqlpostgresqlin-subquery
valodzka
valodzka

Reputation: 5805

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.

Upvotes: 2

Views: 127

Answers (3)

valodzka
valodzka

Reputation: 5805

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)

Upvotes: 0

SingleNegationElimination
SingleNegationElimination

Reputation: 156138

I might write this in terms of set operators:

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

    INTERSECT

    SELECT(<subquery>)
)

Upvotes: 1

user554546
user554546

Reputation:

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>.

Upvotes: 2

Related Questions