It's not a secret anymore!
The moose likes Oracle/OAS and the fly likes Get rows not present in table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Get rows not present in table" Watch "Get rows not present in table" New topic

Get rows not present in table

arun shankar

Joined: Jul 24, 2012
Posts: 13
Hi All,

Can you please help me to achieve below scenario.

I have a table random_numbers, where I have inserted numbers between 1-10 and 80 - 90.

Now, how can I get numbers 11-79 and 91-100 i.e. the numbers which are not present in the table without joining with other tables.

Note: Above values is just an example, i.e. the query or for loop should not be hard coded.

Your help is very much required.

Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

So your goal is to obtain "unused" numbers? It will be a slow, cumbersome and error-prone solution. You should try really hard to avoid it. What's the reason behind the requirement?

If at all possible, you should assign the numbers from a sequence.

The next best approach might be to create a table of available numbers. Picking an available number is then the same as selecting a row (any row) from this table and then deleting it. You'll need to make sure concurrent accesses won't result into two users getting the same number (but this is true for every solution except the sequence, for which this is guaranteed by the database). When the table gets empty, some procedure would populate it with the next batch of available numbers. If numbers can become available later (when a record is deleted from some table, for example), you could add the freed number to the table.

What you describe will require an "antijoin", and it is a relatively expensive DB operation (in relation to the previous solution(s), at least). If you don't want to use any other table, you'd have to generate a table of numbers first using some available Oracle technique, such as select level from dual connect by level <= max_number query. Your final solution could look like this:

You need to know the maximum available number and use it in the query; numbers higher than thousands will probably lead to very bad performance. IF used heavily by many users, you might probably get into trouble much faster with much lower numbers. Moreover, if two users execute this statement in parallel, they'll probably get the same number. If that matters, you'd need to lock the table random_numbers, for example, add the obtained number(s) into it and then commit. Don't you want to use the sequence instead?
arun shankar

Joined: Jul 24, 2012
Posts: 13
Thank you So much,

I just ran nested for loops for this scenario.

But, surely your replies were very helpful.

I agree. Here's the link:
subject: Get rows not present in table
It's not a secret anymore!