Retrieving Unused Unique Values ​​in an SQL Table

Question

I have a table in which there is a column with a description of a numerical identifier that is unique to all rows (but this is not a primary key). The numerical identifier is limited (say for a response, which can be from 1 to 10)

 SELECT ID FROM TABLE;
 ID
 ---
 1
 2
 5

I have to present unused values ​​to the user (via the user interface) in order to select the correct value for the new input. I know how to do this on code (this is a Grails web application), just get the entire identifier from the database and create a list with those that are not. But I want to know if there is a way to do this directly in the SQL query.

So this query should return

ID
---
3
4
6
7
8
9
10

The database is Oracle, but there may be a way to do this for other databases as well.

Answer 1

SELECT  num
FROM    (
        SELECT  level AS num
        FROM    dual
        CONNECT BY
                level <= 10
        )
WHERE   num NOT IN
        (
        SELECT  id
        FROM    mytable
        )

- , dual.

, pseudocolumn level 10. level .

Oracle.

level (aliased as num, ), .

@Abdullah Dogan MINUS. , .

Answer 2

,

select level lvl from dual connect by level<=10
minus
select id from table;