SQL-IN Clause Limit

SQL IN-Clause has both cool and not-so-cool features. This article focuses on a not-so-cool feature which most of us are not aware. Knowing these features and limitations help a database developer and DBA to design a good database, query and stored procedure. It will also cover on how you can convert the not-so-cool feature into a cool one. To explain these not-so-cool feature, we first need to understand how IN clause works.

I have never hit the limit until recently when I have started designing a dashboard in Tableau to monitor the database performance. The dashboard was built using SQL Server database as a data source and worked as designed. The same dashboard when moved to Oracle data source did not work at all. After reading this article, you will find out why it didn't work with Oracle.

Let's begin what IN clause is all about. SQL-IN clause is used after a WHERE condition which allows testing a term of conditions by comparing it for equality with a list of fixed values. The conditions defined within a IN operator are called membership conditions.

IN Clause  SQL Syntax:

Example: 1

--NUMBER membership condition 
SELECT first, 
       last, 
       employee_id 
FROM   employee 
WHERE  employee_id IN ( 243, 
                       233,357);

Example: 2

--CHARACTER membership condition 
SELECT first, 
       last, 
       employee_id 
FROM   employee 
WHERE  last IN ( 'Baniya', 
                'Smith', 
                'Dave');

IN-Clause Properties

1. IN clause works with all data types
2. The data type other than a number should be enclosed with a single or double quotations mark
3. Reduces too many OR conditions under WHERE clause

What's not-so Cool?

We have discussed all the cool things that SQL-IN clause has to offer. Did you know that it has a limitation on how many values you can put inside a IN clause? There is a limit on how many membership conditions you can have within IN clause? The number depends on the database type but you should be aware when writing a query or designing a stored procedure. If you reach a limit, DBMS engine will throw some weird error message. I have listed the error messages that you may expect with Oracle and SQL Server below.

Oracle Database:

Limit: You can have up to 1000 membership conditions.
Error Number: ORA-01795
Error Message: Oracle Error Message: ORA-01795 maximum number of expressions in a list is 1000

SQL Server Database:

Limit: Microsoft hasn't released the exact number but it states many thousands. Well, the limit number seems to be higher than Oracle. I would not write a query that uses more than one thousand as I don't want to take a chance.
Error Number: 8623 or 8632
Error Message:

Error 8623: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Error 8632: Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

Other Databases:

If you are working with database other than Oracle and SQL Server, you need to check their documentation on the limit for IN-clause. They should have some limit number not an infinity. You will get similar error message like Oracle and SQL Server.

Converting Not-So-Cool Into Cool:

To resolve the issue with limit on membership conditions on a query, you will need to create a table or find a table to store the membership conditions that you need to pass inside a IN clause. There are few solutions I can think of:

1. Avoid IN-clause where possible
2. Using IN with a sub-query clause

Solution using Sub-Query:

Oracle:

SELECT first, 
       last, 
       employee_id 
FROM   employee 
WHERE  employee_id IN (SELECT empid 
                       FROM   it_dept); 

SQL Server:

SELECT first, 
       last, 
       employee_id 
FROM   employee 
WHERE  employee_id IN (SELECT empid 
                       FROM   it_dept); 

Other Databases:

SELECT first, 
       last, 
       employee_id 
FROM   employee 
WHERE  employee_id IN (SELECT empid 
                       FROM   it_dept); 

The solution of querying from a table and passing that into a IN-clause resolves the limit issue with any database you might be using in this planet. Where possible, avoid writing a query with hard coded membership conditions values. It is smart to query membership conditions values for a table instead. The another benefit of using a sub-query approach with a table makes the code maintenance super easy.