oracle - SQL multiple columns in IN clause

Question

If we need to query a table based on some set of values for a given column, we can simply use the IN clause.

But if query need to be performed based on multiple columns, we could not use IN clause(grepped in SO threads.)

From other SO threads, we can circumvent this problem using joins or exists clause etc. But they all work if both main table and search data are in the database.

E.g
User table:
firstName, lastName, City

Given a list of (firstname, lastName) tuples, I need to get the cities.

I can think of following solutions.

1

Construct a select query like,

SELECT city from user where (firstName=x and lastName=y) or (firstName=a and lastName=b) or .....

2

Upload all firstName, lastName values into a staging table and perform a join between 'user' table and the new staging table.

Are there any options for solving this problem and what is the preferred of solving this problem in general?

Answer 1

You could do like this:

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

The sqlfiddle.