[SOLVED] SQL Multiple Columns in IN clause to convert to JPA

SQL Multiple Columns in IN clause to convert to JPA

I want to convert/translate the below query with multiple columns in IN clause to JPA.

Query:

SELECT city FROM user WHERE (firstName, lastName) IN (('a', 'b'), ('c', 'd'));

Solution

I don't know what JPA is (I can Google it; what I mean is that I am not familiar with it); but: if there is any hope to handle a SQL query, and the only issue is translating a condition on tuples, then rewrite the query like so:

select city 
from   user
where  firstname = 'a' and lastname = 'b'
   or  firstname = 'c' and lastname = 'd'
;

This is what the query engine will do with your original query regardless; you can look at an EXPLAIN PLAN to convince yourself of this.

Some people like to put parentheses around each pair of AND-connected conditions; I don't, any more than I would use parentheses for 2 * 3 + 4 * 6, but if you feel they add clarity, by all means you can add them.