Is there a limit to the number of arguments in an "IN" statement?

Database
Fan

Is there a limit to the number of arguments in an "IN" statement?

I get lists of acct numbers and find it easier to query against the list by pasting the list into my acct lookup code as opposed to uploading the list to a table.

example:
where acct_nbr in (1234, 3456, 4567....etc)

Due to human error, some acct numbers are bad and don't return results. I just want to make sure I'm not making that assumption when in fact teradata is cutting my list off.

I've read that there is a 1mb sql size limit overall.

Thanks!
4 REPLIES
Enthusiast

Re: Is there a limit to the number of arguments in an "IN" statement?

Not aware of 1MB limit !! where did you get that?
Ideally a sub query serve better purpose , performance wise , if the argument list in the IN clause is greater than 50 values though.
Fan

Re: Is there a limit to the number of arguments in an "IN" statement?

I got the 1 MB from this post:
http://forums.teradata.com/forum/connectivity/any-limitations-with-large-number-10000-parameters-to-sql-select-via-jdbc

I realize the sub query is more efficient, but when you add my time required to create table and upload than pasting is faster.

I assume teradata just sticks my list in an array of some sort, which shouldn't be limited.
Enthusiast

Re: Is there a limit to the number of arguments in an "IN" statement?

From the mentioned link - 1 MB for SQL request text.
It suggest the maximum size limit of a SQL query rather than limiting IN clause values. Therefore, syntax wise you wont have issues with huge IN list but performance wise ,its not advisable.
Enthusiast

Re: Is there a limit to the number of arguments in an "IN" statement?

Teradata has made some improvements to IN list processing but the query must meet certain criteria in order for it to be used. (I believe there is an Orange Book available on the subject.) The former limit of 1024 items has been lifted but you are still restricted by the maximum number of characters in an SQL request.