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?!!

Solution 1

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.

Solution 2

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

Solution 3

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)

Solution 4

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 )

Solution 5

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

Arrays and Lists in SQL Server 2005

View more solutions

Comments

[Bogdan_Ch]

not correct. Max size of SQL instruction or a batch is 65K * (network packet size which is usually 4K) = more then 250 Mb... msdn.microsoft.com/en-us/library/ms143432.aspx .

[tekBlues]

You are damned right on the spot, I complemented the answer. BTW, in ORACLE the limit is VERY easy to reach!.

[dburges]

Better to do a join to the temp table than a subselect usually.

[Lukazoid]

Would an exists clause not be even better?

[ldam]

I don't know why you are being downvoted because this is a completely valid solution. Although I'd change the Select Id FROM @OrderIDs to a join.

[Michael K. Campbell]

IN() isn't SARGable. You're much better off pushing your parameters into a temp table or TVP and then using an INNER JOIN.

[Vikas Sharma]

what is the limit on above query ? I mean how many IN clauses i can define with OR ?

[Jason V]

i know this is an old answer, but do you know what happens to your OrderIds? does it remain? or is it simple a temp file?

[Greg]

@Jason @OrderIds is a table-valued parameter. It only exists for the duration of the procedure.

[TT.]

No, in SQL Server you can not. SQL Server uses T-SQL which is not ANSI SQL compliant (neither is any other dialect).

[ENOTTY]

This ugly hack only works in Oracle SQL, see asktom.oracle.com/pls/asktom/…

[Woland]

@MichaelK.Campbell can you provide some links about "IN() isn't SARGable"? And how significant is it? Perfomance can decrease on 200%?

[wobbily_col]

According to the forum its not faster in the case of SQLite: sqlite.1065341.n5.nabble.com/…

[miradham]

@Woland here is wiki about sargable

[Dzmitry Lahoda]

what error happens when value is too large?

[cloudsafe]

This is my goto solution. Works well called from javascript, C#. The SQL statement ends up containing multiple INSERTs to the table type instance before the query.