Using IN with multiple columns

Question

Just a quick question. I'm using single values manually inputed by the user and doing an SQL query comparing to two columns, like:

SELECT col3,col1,col4 FROM table WHERE
col1='SomeReallyLongText' OR col2='SomeReallyLongText'

The repetition of SomeReallyLongText is tolerable, but my program also supports looping through an Excel document with several hundred rows - which means I'll be doing:

SELECT col3,col1,col4 FROM table WHERE
col1 IN('item1','item2',...'itemN') OR col2 IN('item1','item2',...'itemN')

And the query would be exhaustively long, which I can't imagine is efficient. Is there a way to shorten this so two columns can be compared to the same IN(xxx) set?

If not, are there other (more efficient) ways of giving the set of values in the query?

(I'm using C# with .NET 4.0 Client Profile, using Excel Interop to access the file)

You can use a table valued parameter to pass arrays of things, and not just with DataTables and stored procedures mssqltips.com/sqlservertip/2112/… — ta.speot.is
– ta.speot.is, Commented May 15, 2013 at 10:03

Serge · Accepted Answer · 2013-05-15 09:54:40Z

3

I'm not too sure about the performance you'd get with this:

SELECT col3,col1,col4 FROM table
WHERE EXISTS (
    SELECT 1
    FROM (VALUES
        ('item1')
        , ('item2')
        , ...
        , ('itemN')
    ) AS It(m)
    WHERE It.m IN (col1, col2, ...)
)

edited May 15, 2013 at 9:54

answered May 15, 2013 at 9:46

Serge

6,7425 gold badges33 silver badges58 bronze badges

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

3 Comments

Whoop5 Over a year ago

can the VALUES(('a'),('b'),('c')...('n')) be shortened, maybe to something like VALUES('a','b','c'...'n')?

Serge Over a year ago

Those aren't the same so not with this approach.

Whoop5 Over a year ago

Picked your answer as it doesn't involve creating a new table on the server.

praveen · Accepted Answer · 2013-05-15 10:01:07Z

1

You can create a temp table to store all the values used inside the IN clause

IF OBJECT_ID('tempdb..#Sample') IS NOT NULL DROP TABLE #Sample

Create table #Sample
(name varchar(20))

Insert into #Sample
values
('item1'),('Item2'),....

SELECT col3,col1,col4 FROM table WHERE
col1 IN ( Select name from #Sample) OR col2 IN(Select name from #Sample)

or if you are using Linq to SQL then you can store the excel data in collection and use Contains method to query the DB

var excelVal = new string[] { 'item1','item2'... };
var result = from x in Table
             where excelVal .Contains(x.Col1) || excelVal.Contains(x.Col2)
             select x;

edited May 15, 2013 at 10:01

answered May 15, 2013 at 9:43

praveen

12.3k1 gold badge44 silver badges50 bronze badges

1 Comment

Raj Over a year ago

Try executing the first 3 lines twice in the same session. You will get "There is already an object named '#Sample' in the database." error

Collectives™ on Stack Overflow

Using IN with multiple columns

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related