CODEWITHSUNDEEP
sqlpostgresqlwhere-clause
Sam Broster
Sam Broster

Reputation: 632

Selecting rows where the value of 2 columns occur in some list of tuples

This question is best asked with an example.

Consider a table like:

Main | P1 | P2
==============
1    | 1  | 1
2    | 1  | 3
3    | 1  | 1
4    | 2  | 3
...

I want to issue a query that returns all Main values where the values of P1 and P2 occur in a list of tuples.

For instance, if I had the list [(1,1), (2,3)] I would want rows where:

For the table above that would be the rows where Main is 1, 3 or 4.

I think I can achieve this with something like:

SELECT Main 
WHERE CONCAT(P1, P2) IN ("11", "13")

But I suspect this won't use any of the indexes I have on P1 or P2. Assuming I have a lot of data is there a nicer way to issue this query that will make use of the indexes?

EDIT

I've subsequently found this question which answers my question too (I was using sqlalchemy)

Upvotes: 2

Views: 911

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you just want in with tuples:

where (p1, p2) in ( (1, 1), (2, 3) )

Upvotes: 5

Related Questions