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

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)

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 )

Related