48

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?

Improve this question
6
  • 1
    You can concatenate fields too. Commented Oct 23, 2012 at 9:49
  • 2
    Which RDBMS? Different RDBMS have different capabilities. Oracle, for example can do WHERE (Field1, Field2) = ('a', 'b') but MySQL can't. [That typical approach is to supply the list as a string or xml, convert that into a data-set using a function, and then join on to that data-set.] Commented Oct 23, 2012 at 9:50
  • Where are your first and last name values now? spreadsheet? csv? Commented Oct 23, 2012 at 10:15
  • 1
    firstName and lastName values will be passed to the framework as simple List. Commented Oct 23, 2012 at 10:48
  • @Htaras What Framework though? Is it a .net List<string>? How are you connecting to the DB? Also what version of SQL? SQL Server, Oracle, MySql? Commented Oct 23, 2012 at 10:50

7 Answers 7

Reset to default
111

You could do like this:

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

The sqlfiddle.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Not in ALL RDBMS you can't. For example; Oracle can, but MySQL can't.
@Dems Check the sqlfiddle I added.
This corporate laptop doesn't play well with SQL Fiddle (IE7, security setting, etc), but I can see that you've set it up for MySQL 5.5.27? Does it work in 5.1? And/or SQL Server?
@Dems I am new to Oracle. Does it work well with prepared queries in Oracle. "SELECT city FROM user WHERE (firstName, lastName) IN(?)" and ? = "('a', 'b'), ('c', 'd')"
Does oracle 11g supports this? I tried this and I get an error, Invalid relational operator.
|
8

In general you can easily write the Where-Condition like this:

select * from tab1
where (col1, col2) in (select col1, col2 from tab2)

Note
Oracle ignores rows where one or more of the selected columns is NULL. In these cases you probably want to make use of the NVL-Funktion to map NULL to a special value (that should not be in the values):

select * from tab1
where (col1, NVL(col2, '---') in (select col1, NVL(col2, '---') from tab2)

Improve this answer

Comments

5

It often ends up being easier to load your data into the database, even if it is only to run a quick query. Hard-coded data seems quick to enter, but it quickly becomes a pain if you start having to make changes.

However, if you want to code the names directly into your query, here is a cleaner way to do it:

with names (fname,lname) as (
    values
        ('John','Smith'),
        ('Mary','Jones')
)
select city from user
    inner join names on
        fname=firstName and
        lname=lastName;

The advantage of this is that it separates your data out of the query somewhat.

(This is DB2 syntax; it may need a bit of tweaking on your system).

Improve this answer

6 Comments

+1 for the set-based approach. Would be nice to see the plain-jane SELECT a,b UNION ALL SELECT c,d subquery version that works everywhere except Oracle
@RichardTheKiwi - with judicious use of FROM dual your plain-jane pattern does work in Oracle.
@RichardTheKiwi - Apologies, I didn't mean to cause offense. Just to clarify something for the OP and other readers.
@RichardTheKiwi, that doesn't work cut and paste in DB2 either; you have to add from sysibm.sysdummy1. Thanks for the suggestion that helps make the answer more widely useful, though.
@dan For some reason I never considered DB2 a major RDBMS :) It never comes up when I list RDBMS in comments. Among Wikipedia's top 3 commercial, 3 open source (en.wikipedia.org/wiki/Relational_database) I have worked on all but DB2. By StackOverflow tag, DB2 is sorely behind the other 5
|
5

In Oracle you can do this:

SELECT * FROM table1 WHERE (col_a,col_b) IN (SELECT col_x,col_y FROM table2)
Improve this answer

1 Comment

This does not work for me in oracle (using a more complex query). I suggest to use an inner join instead.
2

Ensure you have an index on your firstname and lastname columns and go with 1. This really won't have much of a performance impact at all.

EDIT: After @Dems comment regarding spamming the plan cache ,a better solution might be to create a computed column on the existing table (or a separate view) which contained a concatenated Firstname + Lastname value, thus allowing you to execute a query such as 

SELECT City 
FROM User 
WHERE Fullname in (@fullnames)

where @fullnames looks a bit like "'JonDoe', 'JaneDoe'" etc

Improve this answer

6 Comments

Except that, depending on the RDBMS, this will spam the plan cache. A query with a list of 2 people will have a different signature from a query with a list of 3 people, etc, etc.
That's a fair point. how about if you were to construct a view that contained a concat of the firstname and last name, or created a computed column on the existing table, then you could use a statement such as where fullname in @fullnames
Option 1 would be an unreadable mess if you have more than a few pairs of names. And rather than going to all the trouble of concatenating the first and last names and making a view (which would be bad from a performance perspective) why not just load the data into a table?
@JaimalChohan, simply declaring a temporary table with the data hard-coded in it does not seem any harder than your suggestion. And you can effectively do the same thing in a single query, as well--see my answer. There is no reason to add the complexity of concatenating fields.
So would joamos be joa-mos or jo-amos? I don't like this solution at all.
|
0

Determine whether the list of names is different with each query or reused. If it is reused, it belongs to the database.

Even if it is unique with each query, it may be useful to load it to a temporary table (#table syntax) for performance reasons - in that case you will be able to avoid recompilation of a complex query.

If the maximum number of names is fixed, you should use a parametrized query.

However, if none of the above cases applies, I would go with inlining the names in the query as in your approach #1.

Improve this answer

2 Comments

even though though there is no explicit rule in my code, I don't expect more than 100 names. How can it be parametrized, is it different from normal parameterization? SELECT city FROM user WHERE (firstName, lastName) IN(?)" and ? = "('a', 'b'), ('c', 'd')"
It is the same as normal parametrization, one question mark per individual string. However, 100 is a large value, especially if 99% of queries are going to use exactly one name. I would not parametrize in this case. Oracle is smart enough to reuse the same plan whenever just the number of names is identical.
0 
SELECT A,B,C FROM tabel1 where (tabel1.D,tabel1.E) in (SELECT tabel2.A,tabel2.B FROM tabel2 WHERE tabel2.Z);

We can give multiple conditions to the WHERE condition, then we can extract matching records only.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.