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?

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.]* — MatBailie, Oct 23 '12 at 09:50
Where are your first and last name values now? spreadsheet? csv? — Tobsey, Oct 23 '12 at 10:15
firstName and lastName values will be passed to the framework as simple List. — Htaras, Oct 23 '12 at 10:48
@Htaras What Framework though? Is it a .net List? How are you connecting to the DB? Also what version of SQL? SQL Server, Oracle, MySql? — Tobsey, Oct 23 '12 at 10:50

xdazz · Accepted Answer · 2012-10-23T10:06:42.530

109

You could do like this:

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

The sqlfiddle.

edited Oct 23 '12 at 10:06

answered Oct 23 '12 at 09:49

xdazz

158,678
38
247
274

5

Not in *ALL* RDBMS you can't. For example; Oracle can, but MySQL can't. – MatBailie Oct 23 '12 at 09:51
@Dems Check the sqlfiddle I added. – xdazz Oct 23 '12 at 10:07
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? – MatBailie Oct 23 '12 at 10:14
@Dems I did not test it in old version of MySQL, at least the syntax works in current stable version of MySQL. – xdazz Oct 23 '12 at 10:16
2

@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')" – Htaras Oct 23 '12 at 10:52
In DB2 syntax is slightly different (source: http://stackoverflow.com/questions/10725901/select-query-by-pair-of-fields-using-an-in-clause) SELECT city FROM user WHERE (firstName, lastName) IN (VALUES ('a', 'b'), ('c', 'd')); – pierpytom Oct 30 '15 at 13:57
2

Does oracle 11g supports this? I tried this and I get an error, Invalid relational operator. – Tom J Muthirenthi Feb 21 '17 at 14:02
@TomJMuthirenthi Oracle 11g does support this. Not sure when support was added, but I have no problems with the above syntax. Works with hardcoded strings as well as selecting from tables. – Joseph May 05 '17 at 15:26
Doesn't work on sqlite3. `Result: row value misused` – carkod Apr 29 '20 at 16:57
when query goes below `SELECT city FROM user WHERE (firstName) IN ('a','c'); ` how can I order result city values according to where clause input order `ex : a-->city1 c-->city2` – max May 20 '22 at 06:16

score 5 · Answer 2 · answered Oct 23 '12 at 09:58

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).

answered Oct 23 '12 at 09:58

dan1111

6,576
2
18
29

+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 Oct 23 '12 at 10:07
@RichardTheKiwi - with judicious use of `FROM dual` your `plain-jane` pattern *does* work in Oracle. – MatBailie Oct 23 '12 at 10:16
@RichardTheKiwi - Apologies, I didn't mean to cause offense. Just to clarify something for the OP and other readers. – MatBailie Oct 23 '12 at 10:25
@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. – dan1111 Oct 23 '12 at 10:26
@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 (http://en.wikipedia.org/wiki/Relational_database) I have worked on all but DB2. By [StackOverflow tag](http://stackoverflow.com/tags), DB2 is sorely behind the other 5 – RichardTheKiwi Oct 23 '12 at 10:32
@RichardTheKiwi, fair enough. It is just the one I am stuck with, unfortunately. I only wanted to mention that as a clarification. – dan1111 Oct 23 '12 at 10:38

score 5 · Answer 3 · answered Feb 10 '17 at 07:23

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)

oracle sql

score 4 · Answer 4 · edited Oct 21 '15 at 01:12

4

In Oracle you can do this:

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

edited Oct 21 '15 at 01:12

geekzspot

787
7
7

answered Oct 19 '15 at 20:15

grokster

5,919
1
36
22

This does not work for me in oracle (using a more complex query). I suggest to use an inner join instead. – aturegano Dec 14 '22 at 11:04

Jaimal Chohan · Answer 5 · 2012-10-23T10:08:08.363

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

edited Oct 23 '12 at 10:08

answered Oct 23 '12 at 09:51

Jaimal Chohan

8,530
6
43
64

2

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. – MatBailie Oct 23 '12 at 09:54
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` – Jaimal Chohan Oct 23 '12 at 09:57
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*? – dan1111 Oct 23 '12 at 10:07
@dan1111 is it not a bit excessive having to insert the data into a table, and then join against it, and then clear it down. That's 3 queries. The view could be an indexed view if the data didn't change constantly. – Jaimal Chohan Oct 23 '12 at 10:10
1

@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. – dan1111 Oct 23 '12 at 10:24
2

So would `joamos` be `joa-mos` or `jo-amos`? I *don't* like this solution at all. – RichardTheKiwi Oct 23 '12 at 10:24

score 0 · Answer 6 · answered Oct 23 '12 at 09:54

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.

answered Oct 23 '12 at 09:54

Jirka Hanika

13,301
3
46
75

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')" – Htaras Oct 23 '12 at 10:54
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. – Jirka Hanika Oct 23 '12 at 11:25

SQL multiple columns in IN clause

1

2

6 Answers6

Linked

Related