In case you wonder what it the maximum number of values you can put inside the IN condition, it is 1000 as of Oracle 9i. It is documented in the link below.
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96536/ch44.htm#288033.

Example:

SELECT * FROM emp
WHERE emp_no IN(1,2,3,4,5......1000);

I needed this because I was given a long list of values (around 500 values) and I need to fetch records in a table using that data. It would be easy if I can insert it first into a temp table and then join. Unfortunately, I only have read rights on the database so I need to use the IN condition. But I got concerned because in Oracle 7 and 8i releases, the maximum number of values you can put inside IN is a maximum of 255 (one byte).

Note:

Take note that the IN(SUBQUERY) does not have limitations because that’s essentially a join.

Example:
SELECT * FROM emp
WHERE emp_no IN(SELECT emp_no FROM dept_emp);

However as part of my best practices, I make sure that the results of the IN subquery is either one or none for optimal performance (not applicable in some instances).

Comments are closed.

Post Navigation