Maximum length of WHERE clause with IN statement

Question

I need to get all features from feature class where their field value matches to input array. To achive this one may set IQueryFilter.WhereClause property similar to 'FIELD_NAME IN (array[0], array[1], array[2],...., array[n-1])'. But the size of array might be quite big.

The questions are how long IN statement can be? And can I define it at runtime?

Thanks in advance!

Are you doing this via code, or in the User interface? How is the array currently stored? — Devdatta Tengshe, Nov 13 '13 at 07:26
This depends on the underlying database. Note that besides the total query length, some databases also impose restrictions on the count of elements in a single IN clause. — Petr Krebs, Nov 13 '13 at 08:51
I'm doing it through the C# code. Array is rarefied.

"some databases also impose restrictions on the count of elements in a single IN clause." - I expect this. That's why I'm asking can I define this at runtime. — Eduard Lepner, Nov 13 '13 at 11:17
You'd need to specify which RDBMS you're using in order to get an answer — Vince, Nov 13 '13 at 13:29
Now we are targeting on Oracle. But I'd like to support SQL and personal gdb also. — Eduard Lepner, Nov 13 '13 at 17:40
"SQL" isn't an RDBMS, but "SQL-Server" is (Microsoft product). Personal geodatabase isn't SQL-based (Microsoft Jet/Access). They each have different IN clause limitations. — Vince, Nov 14 '13 at 12:18
Related: ArcObjects: Alternative workflow to using a SQL query with over 1000 values in IN statement — blah238, Nov 14 '13 at 16:57

score 7 · Accepted Answer · answered Nov 14 '13 at 14:19

The exact number of explicit terms in an IN clause varies by RDBMS. Oracle limits you to 1000 explicit terms, while the SQL-Server doc only mentions "several thousands".

The real issue is that an IN clause is not very efficient. If you have more than a few hundred terms, you'd likely get a faster query by loading a temporary table with the values, then doing a join (or an IN to a subselect, which is implemented as a join). In ArcGIS, selection sets can be used for this purpose (vice explicitly creating a temporary table, but the implementation is the same).

score 2 · Answer 2 · answered Nov 14 '13 at 10:00

I didn't find a way how to find out exact size of IN statement and then safaly build it but I have found a workaround. I have reduced described problem to search the rows satisfying the following where clause: (OBJECTID IN (123, 456,789)) AND FIELD1 = x AND FIELD2 = y AND .... I.e. the statement has IN statement with very big list of numbers and arbitrary AND statements.

Here is the workaround:

I used ITable.Select to create empty selection set.
Then populate this set with ISelectionSet.AddArray with necessary OIDs. It allows to avoid from using IN statement like in WHERE clause OBJECTID IN (123, 456, 789)
Apply additional additional where caluses to selection set ISelectionSet.SearchSearch(IQueryFilter, bool, out ICursor).

score 0 · Answer 3 · answered Dec 01 '14 at 15:08

How can the below be replaced to use a variable in place of hard code values filter.WhereClause = featureClass.OIDFieldName + " IN (1,2,3,4,5,6)";

string strFIDS="1,2,3,4,5,6";

Does the below work ? filter.WhereClause = featureClass.OIDFieldName + " IN (strFIDS)";

Maximum length of WHERE clause with IN statement

3 Answers3