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 1:

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
wherefirstname='a'andlastname='b'orfirstname='c'andlastname='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.

Solution 2:

You could use some sort of a subquery, which i think would be more relevant than a set of explicitly defined pairs (you can also stack it up with or statements for more complex cases):

SELECT city 
FROMuserWHERE (firstName, lastName) IN (
     select stringOne, stringTwo from TableOne t ...
   )
   or
   (firstName, lastName) IN (
     select stringOne, stringTwo from TableTwo t ...
   )