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

Split the numbers in the IN clause up by groups large enough to process, and async them all at the same time. — Travis J, Dec 01 '14 at 23:39
Actaully do not use in - IN is bad because it contains no statistical information. Declare a table valued type of the keys with a primary key (then it has statistical values attached), insert the values there, JOIN between table and that temp table and the query optimizer can do it's work. — TomTom, Feb 28 '20 at 20:43

score 73 · Accepted Answer · edited Mar 28 '12 at 13:45

73

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.

edited Mar 28 '12 at 13:45

xanadont

7,493
6
36
49

answered Jul 01 '09 at 14:28

tekBlues

5,745
1
29
32

5

not correct. Max size of SQL instruction or a batch is 65K * (network packet size which is usually 4K) = more then 250 Mb... http://msdn.microsoft.com/en-us/library/ms143432.aspx . – Bogdan_Ch Jul 01 '09 at 14:43
1

You are damned right on the spot, I complemented the answer. BTW, in ORACLE the limit is VERY easy to reach!. – tekBlues Jul 01 '09 at 14:50
According to the forum its not faster in the case of SQLite: http://sqlite.1065341.n5.nabble.com/Is-there-a-limit-for-the-number-of-items-in-an-IN-clause-td81307.html – wobbily_col Apr 16 '20 at 13:05

score 37 · Answer 2 · answered Jul 01 '09 at 14:29

37

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

answered Jul 01 '09 at 14:29

Iain Hoult

3,889
5
25
39

1

IN() isn't SARGable. You're much better off pushing your parameters into a temp table or TVP and then using an INNER JOIN. – Michael K. Campbell Apr 24 '17 at 19:01
what is the limit on above query ? I mean how many IN clauses i can define with OR ? – Vikas Sharma Jun 12 '17 at 03:53
1

@MichaelK.Campbell can you provide some links about "IN() isn't SARGable"? And how significant is it? Perfomance can decrease on 200%? – Woland Feb 25 '19 at 09:54
@Woland here is [wiki](https://en.wikipedia.org/wiki/Sargable) about sargable – miradham May 04 '20 at 16:57
However that wiki specifically lists `IN` as a "sargable operator". – Jasper-M Sep 01 '23 at 08:36

score 13 · Answer 3 · edited Nov 30 '18 at 23:35

13

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)

edited Nov 30 '18 at 23:35

Dave Jarvis

30,436
41
178
315

answered May 16 '13 at 15:03

Greg

23,155
11
57
79

2

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. – ldam Jul 05 '16 at 08:37
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? – Jason V Jul 26 '17 at 15:26
1

@Jason `@OrderIds` is a table-valued parameter. It only exists for the duration of the procedure. – Greg Jul 26 '17 at 15:34
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. – cloudsafe Jul 14 '21 at 12:47

score 5 · Answer 4 · edited May 21 '13 at 03:47

5

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 )

edited May 21 '13 at 03:47

Jesse

8,605
7
47
57

answered Jul 01 '09 at 14:29

DRapp

47,638
12
72
142

3

Better to do a join to the temp table than a subselect usually. – HLGEM Jul 01 '09 at 18:43
Would an exists clause not be even better? – Lukazoid Mar 06 '14 at 11:10

score 4 · Answer 5 · answered Jul 01 '09 at 14:31

4

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

Arrays and Lists in SQL Server 2005

answered Jul 01 '09 at 14:31

A-K

16,804
8
54
74

score 1 · Answer 6 · answered Feb 28 '20 at 20:39

1

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)

answered Feb 28 '20 at 20:39

roncansan

2,310
6
27
34

what error happens when value is too large? – Dzmitry Lahoda May 25 '21 at 04:16

score 0 · Answer 7 · edited Oct 21 '21 at 07:12

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.

score 0 · Answer 8 · answered Oct 17 '21 at 06:28

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.

score -4 · Answer 9 · answered Oct 10 '17 at 07:35

-4

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.

answered Oct 10 '17 at 07:35

Lukasz

1

No, in SQL Server you can not. SQL Server uses T-SQL which is not ANSI SQL compliant (neither is any other dialect). – TT. Oct 10 '17 at 07:47
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 – ENOTTY Jan 23 '19 at 09:22

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

9 Answers9

Linked

Related