jDeveloper jDeveloper - 3 years ago 149
SQL Question

Limit on the WHERE col IN (...) condition

I'm using the following code:

SELECT * FROM table
WHERE Col IN (123,123,222,....)


However, if I put more than ~3000 numbers in the
IN
clause, SQL throws an error.

Does anyone know if there's a size limit or anything similar?!!

Answer Source

Depending on the database engine you are using, there can be limits on the length of an instruction.

SQL Server has a very large limit:

http://msdn.microsoft.com/en-us/library/ms143432.aspx

ORACLE has a very easy to reach limit on the other side.

So, for large IN clauses, it's better to create a temp table, insert the values and do a JOIN. It works faster also.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download