WHERE UserID IN (......) limitations

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

WHERE UserID IN (......) limitations

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2007-05-03 : 09:44:16

Are there any limitations to the number of values in the IN clause?

I heard that some databases limit the number of values one can put in the IN clause, does SQL Server have one? Does it matter if one does:

WHERE UserID IN (1,3,4,5,3,2,....)

versus

WHERE UserID IN (select userid from users)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-03 : 09:48:26

Same thing.

Try

SELECT t1.UserID
FROM Table1 AS t1
WHERE EXISTS (SELECT * FROM Table2 AS t2 WHERE t2.UserID = t1.UserID)

or

SELECT t1.UserID
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.UserID = t1.UserID

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-03 : 09:51:24

I have never heard of any such limitation.

But normally I hate to see IN clause in my sql statements unless it is containing literals or used in correlated subquery. I prefer JOIN or EXISTS over it.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 09:54:22

well let me tell you that having 10000 values in the "in" isn't pretty or fast

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-03 : 09:57:40

This is off-topic but MS Access had a limitation of 24 items in the IN list, before.
I don't know if there's a limitation with MS Access 2007.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-03 : 09:58:06

quote:
Originally posted by spirit1

well let me tell you that having 10000 values in the "in" isn't pretty or fast

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

I have to ask.

What lead you to actually try this?

CODO ERGO SUM

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2007-05-03 : 10:05:45

Well some people on my team are weary of joining on the Users table since it contains say 1 million + rows so they suggested to do a where IN.

I'm pretty sure a join will be faster, I'll test it on profiler to get a more accurate query time since QA just shows how many seconds it took.

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2007-05-03 : 10:08:27

In QA the query using IN and the other using an INNER JOIN seem to be the same speed, again i'll do it in profiler shortly.

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2007-05-03 : 10:10:23

Someone else mentioned this:

SELECT *
FROM Blah
WHERE UserID = n OR UserID = n2 OR UserID=n3 .......

So basically I would have a potential of 300-400 OR's in my WHERE clause.

Comments?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-03 : 10:16:07

quote:
Originally posted by sql777

In QA the query using IN and the other using an INNER JOIN seem to be the same speed, again i'll do it in profiler shortly.

That's probably because Query Optimizer is smart enough to convert your IN clause into equivalent JOIN itself while generating execution plan.

Having multiple ORs is out of question, since it is same as IN (...), unless of course you want to create nightmare for the person supporting it in future.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 10:24:00

> So basically I would have a potential of 300-400 OR's in my WHERE clause.
yes you would. in translates to OR's. if you can change it to a range scan with userid >= n and userid <= nn it would be great.
You don't need profiler. use SET STATISTiCS TIME ON

MVJ:
when i came to the place i work at the moment there was this old asp app that did in code of course:
"where in (" & sGetSomeIds() & ")"
and the function sGetSomeIds returned a CSV of some id's from a table.

well after 6 months that page got really slow. so i fired up profiler and there it was:
around 10000 CSV'ed GUID's were being returned by this sGetSomeIds().
and it was so that it retunred all 10000 to the web server which then looped throug the adOpenKeyset recorders and concating them there...
a simple join fixed stuff

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-03 : 10:27:55

One caveat about using JOIN is the possibility of having/creating duplicate rows.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources