Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Alternatives to IN in Oracle

Posted on 2011-10-03
Medium Priority
Last Modified: 2013-12-19
I have a table that has 7 million unique ID's.
I'm looking for 24k that are coming off a spreadsheet.

I have read only access to the tables, so I don't think I can create
a temp table to join off of for filtering.

What would be the best way to return the ID's I want, and their
corresponding fields, given that IN only allows 1,000 arguments (I think)?
Question by:JustinW
  • 2
  • 2
LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 36905086
select * from yourtable where yourid in (1,2,3,....1000)
or yourid in (1001,1002,...., 2000)
or yourid in (2001, ...., 3000)


change the inlists to be your actual values.


Author Comment

ID: 36905240
Is this the fastest way?
Is there not like a CTE thing you can do in oracle, like in SQLServer?
LVL 74

Expert Comment

ID: 36905336
sure you could but it's unlikely to be faster
probably not slower either  I'd expect them to be about the same

if you like cte better, try this...

with cte as (select 1 n from dual union all select 2 from dual union all select 3 from dual
union all .....
select 24000 from dual)
select * from yourtable where yourid in (select n from cte)

Author Closing Comment

ID: 36905352
thanks for doing both!

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month11 days, 16 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question