Post reply

Is there a limit on the number of values that can be used in an IN clause?

  • July 7, 2011 at 5:58 am

    #144128

    Hi

    Does anybody know if here is a limit on the number of records that can be used in an 'IN' clause when used as a sub select. please see example below.

    Delete from Table_A

    where [column_x] in (select distinct column_Y from table_B)

    The sub select statement could contain 1000's of rows does anybody know if there is a limit?

    Thanks in advance

    Darren

  • July 7, 2011 at 6:02 am

    #1349598

    nope no practical limit; that table could be billions of rows in the subquery;

    the only practical limit might be speed; proper indexing might need to be looked at so you don't wait hours or minutes instead of seconds for results.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 7, 2011 at 6:04 am

    #1349601

    Just as a 'by the way'. You don't need the distinct in the subquery at all.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 7, 2011 at 6:23 am

    #1349627

    Thanks for the prompt response very much appreciated.

    p.s. is that a hungarian Vizsla my brother has just picked up a 10 week old puppy.

  • July 7, 2011 at 6:25 am

    #1349629

    Thanks for the tip.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply