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

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.

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,....)

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 )

Depending on your version, use a table valued parameter in 2008, or some approach described here:

Arrays and Lists in SQL Server 2005

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)

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.

I solved this by simply using ranges

WHERE Col >= 123 AND Col <= 10000

then removed unwanted records in the specified range by looping in the application code. It worked well for me because I was looping the record anyway and ignoring couple of thousand records didn't make any difference.

Of course, this is not a universal solution but it could work for situation if most values within min and max are required.

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.