CODEWITHSUNDEEP
sqlpostgresqlselectwhere-in
MiketheCalamity
MiketheCalamity

Reputation: 1269

sql - Dynamically select using two lists for the where clause

I have two lists of values of variable but equal lengths.

Example:
vals1: a, b, c
vals2: 1, 2, 3

What is the best way to do something equivalent to:

select * from table where (col1=vals1[0] and col2=vals2[0]) or (col1=vals1[1] and col2=vals2[1]) or (col1=vals1[2] and col2=vals2[2])

Keeping in mind that the length of the lists can be 1 or more and always the same length. I'd love to not loop and build the string if another option exists (preferably in the form of a SQL query). Any help would be appreciated, thanks.

Upvotes: 1

Views: 1240

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125234

unnest the arrays in parallel

select *
from t
where (col1, col2) in (
    select (a,b)
    from (
        select unnest(array['a','b','c']), unnest(array[1,2,3])
    ) s (a,b)
)

Upvotes: 1

Related Questions