Limit of selections in IN clause

Posted on 2004-11-18
Last Modified: 2008-03-10
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.
Question by:koppcha
    LVL 14

    Expert Comment

    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 )"
    LVL 29

    Accepted Solution

    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.



    Expert Comment

    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.

    LVL 6

    Expert Comment

    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.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now