Results 1 to 9 of 9

Thread: 'IN' keyword

  1. #1
    Join Date
    Jan 2003
    Posts
    10

    Question Unanswered: 'IN' keyword

    hi all,

    does anybody know if there's a maximum on params specified in a query with an 'IN' keyword like:

    select * from table where id in (1,2,3,4,5)

    i've tried putting alot numbers in there but it didnt seem to reach a mazimum really :/

    thnx

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I looked but couldn't find any comment about a limit. I am sure there is one but remember that this function also has to accept the results of a sub-querry. I doubt you will run into a problem.

    How many numbers are you planning on specifying?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2003
    Posts
    10
    in the worst case a few thousand.
    i just tried to give it +/- 10.000 params and it didnt return any errors.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I have seen querries that returned a little over a million records so I would think you are okay.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you actually hard-coding all 10,000+ parameters?
    If you are referencing a table of values, then the optimizer will probably convert your statement into a more logical table join, so there would be no limit.

    If you post what you are trying to accomplish somebody here might be able to suggest a more efficient method.

    blindman

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    a more efficient method? Isn't it obvious? Don't use IN!

  7. #7
    Join Date
    Jan 2003
    Posts
    10
    Originally posted by blindman
    Are you actually hard-coding all 10,000+ parameters?
    no, of course im not hardcoding 10,000 params. those are a result of another query
    If you are referencing a table of values, then the optimizer will probably convert your statement into a more logical table join, so there would be no limit.
    yea i think so

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then its probably best to explicitly use a join instead of IN, just in case the optimizer fails to convert it implicitly.

    blindman

    PS: as far as hard-coding 10,000 parameters, you'd be surprised what I've seen people try to do!

  9. #9
    Join Date
    Jan 2003
    Posts
    10
    k, thanx
    going to try that.

    and yea ive seen people doing such things too

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •