Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3940
  • Last Modified:

Limit of selections in IN clause

In a SQL select statement what is the limit for choices that i can give in a IN clause.For instance in the following example
select * from titles where title_id IN ( a,b,c,d,.....How far can it go.) What is the limit.
0
koppcha
Asked:
koppcha
1 Solution
 
Jan FranekCommented:
I haven't found any limit in manual, so I believe, that it's big enough :-) However, if you reach the limit, you can always by-pass it by storing your choices into some table and then use "select * from titles where title_id IN ( select choise from mychoices )"
0
 
leonstrykerCommented:
I am not sure about a limit. But I have done things like:

select * from titles where title_id IN (Select title_id From authors)

with the authors table containing well over 50,000 records.

However, I must point out that you would be better of avoiding such syntax if possible and use a JOIN if you can.  Even to a point of placing the values of the IN clause into a temp table.

Leon

0
 
cckumarCommented:
I infact tend to believe there is an inherent sybase limit for a list of items that are explicitly listed out in the in clause, but not for nested SQL statements.

The limit on 12.5 appears to be 439. Try it and let me know.

I am postively certain that on older versions this limit used to be 200 items. So appears as if it has been increased.

Thanks.
0
 
ChrisKingCommented:
I can advise from experience that under 12.5.x that a list id 30 id's may not use the index, whereas 10 then 10 then 10 will always use the appropriate index.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now