Limit on the WHERE col IN condition

Question

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

User · Accepted Answer

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.

User · Answer

You did not specify the database engine in question  in Oracle  an option is to use tuples like this   SELECT   FROM table WHERE  Col  1  IN   123 1   123 1   222 1         This ugly hack only works in Oracle SQL  see https   asktom oracle com pls asktom asktom search tag limit-and-conversion-very-long-in-list-where-x-in 9538075800346844400  However  a much better option is to use stored procedures and pass the values as an array

User · Answer

For MS SQL 2016  passing ints into the in  it looks like it can handle close to 38 000 records   select   from user where userId in  1 2 3 etc

User · Answer

You can use tuples like this  SELECT   FROM table WHERE  Col  1  IN   123 1   123 1   222 1         There are no restrictions on number of these  It compares pairs

User · Answer

There is a limit  but you can split your values into separate blocks of in    Select    From table  Where Col IN  123 123 222       or Col IN  456 878 888

User · Answer

Depending on your version  use a table valued parameter in 2008  or some approach described here   Arrays and Lists in SQL Server 2005

User · Answer

Parameterize the query and pass the ids in using a Table Valued Parameter   For example  define the following type   CREATE TYPE IdTable AS TABLE  Id INT NOT NULL PRIMARY KEY    Along with the following stored procedure   CREATE PROCEDURE sp  Procedure Name      OrderIDs IdTable READONLY  AS      SELECT       FROM table     WHERE Col IN  SELECT Id FROM  OrderIDs

User · Answer

Why not do a where IN a sub-select     Pre-query into a temp table or something     CREATE TABLE SomeTempTable AS     SELECT YourColumn     FROM SomeTable     WHERE UserPickedMultipleRecordsFromSomeListOrSomething   then     SELECT   FROM OtherTable WHERE YourColumn IN   SELECT YourColumn FROM SomeTempTable

[sql] Limit on the WHERE col IN (...) condition

Examples related to sql

Examples related to sql-server

Examples related to tsql